Click here to Skip to main content
16,017,100 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
 
Share this answer
 
Comments
moral_2011 26-Jan-11 2:18am    
Thanks Alot For Your Efforts This Exactly What I Was Searching For.......
ahsan sarfraz 26-Jan-11 2:29am    
you are welcome. :)
moral_2011 27-Jan-11 3:30am    
dear all,

About your Solution I have Made afunction in sql 2008 But i could'nt make the same steps and i counld'nt replace any text ,so any suggestion .
Thanks
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
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900