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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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