How Can I Alter "user type" of Paramter in stored procedure?

Question

0.00/5 (No votes)

See more:

Dear All,

I am Working In Small Project Based On Dynamic SQL Created On Run Time,

And I Have A problem in How Can i Alter The Data Type Of specific Parameter In specific Stored Procedure

With Considering That I have Found a Solution Of My Problem But In Table In This Link
http://efreedom.com/Question/1-1383494/Alter-User-Defined-Type-Sql-Server

So ,How Can i Make a solution Near To this solution but for stored Procedure Or Any Solution.

Thanks,

Posted 25-Jan-11 1:54am

moral_2011

Add a Solution

2 solutions

Solution 1

Have you considered not using stored procedures? You said that you're creating dynamic SQL at run time, why not just run that against the DB?

If you have to run it through a stored procedure, you could use sp_executesql stored procedure. Check out the MSDN documentation[^]. Sorry if this isn't what you're after. Just trying to help out!

Hogan

Posted 25-Jan-11 5:46am

snorkie

Comments

moral_2011 25-Jan-11 13:20pm

Thanks Alot For your Answer,

But My Question Now, How Can I Build acommand to alter a specific Parameter in My Stored Procedure "For General"

i Hope To findSomeThing Near To This But For a Stored Procedure:

Ex: Ater table dbo.+ TABLE_NAME +

alter column + COLUMN_NAME + t_myudt_tmp Thanks,

snorkie 25-Jan-11 14:39pm

Is this something that needs to be real time, or a one time change. For a one time change, I recommend using a tool such as Red Gate SQL Compare(http://www.red-gate.com/products/sql-development/sql-compare/) they have a 14 day trial to see if you like it.

If this is something that needs to be dynamic, can you provide a sample table with columns and how you need it changed. Hopefully you don't have to change column types often at run time.

Finally as a crazy idea, and one that I don't recommend, why don't you put all of your variables in the database under the nvarchar type. Then when you put anything in, you do a .ToString() and when you pull it out, convert it back to the intended type.

moral_2011 26-Jan-11 2:19am

Thanks Alot I Will Search On This Tool

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ahsan sarfraz · Accepted Answer · 2011-01-25T18:22:00

Hey,
If i have understood your problem correctly. I have one thing in my mind to do that.

Lets assume that "spFoo" is your stored procedure.you can get the complete sp code by executing the following command.

sp_HelpText spFoo

store the result in a string builder object /String.

string strCommand=(result from above command)

The result returned from the above command will be the Create command of the sp. but here we need an Alter command. so we will first replace the first occurance of "Create" in the strCommand with "Alter".Secondly,lets assume you have a parameter named "@Parameter"
Replace "@Parameter" in the string with the name you want.
eg.
strCommand.Replace("@Parameter","@NewParameterName");

and then execute an SQLCommand having command text= strCommand
(which now has the sql code to alter the procedure with your new parameter name.)

and there you go.
Hope this will help you.Let me know if you have any furthur query.
Regards
Ahsan

Dont forget to mark as answered if the info was useful.Thanks

How Can I Alter "user type" of Paramter in stored procedure?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How Can I Alter "user type" of Paramter in stored procedure?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us