Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-03 : 07:48:03
|
| Peter writes "Hi,I read an article on 4GuysFromRolla about using Dynamic Sql in a Stored Procedure. My question is: Is there anyway to assign a @Parameter's datatype at runtime? The following code demonstrates what I want to do.CREATE PROCEDURE someProc( @Parameter1 int, @Parameter2 varchar (255), @Parameter3 int)AS EXEC('UPDATE tablename SET ' + @Parameter2 + '=''' + @Parameter3 + ''' WHERE field3 = ' + @Parameter1) RETURN As you see, @Parameter 3 is declared as an int. But I have situations in which I will want to pass a string or a datetime depending on the dynamically assigned fieldname (@Parameter2). Right now, I use several slightly different stored procs to handle the different cases. Is there a way to dynamically declare the parameter data type?Peter" |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-10-03 : 08:04:56
|
Search this site for "dynamic SQL" and you'll see why it isn't such a good idea. But that another topic...to answer your question: you could use the varchar datatype to accpept all types of parameters, it can store most datatypes and is easily converted into the required datatype. Look up the CAST and CONVERT functions in the Books Online, they can be used to convert the parameter across datatypes. Also look at ISNUMERIC and ISDATE.Owais
Make it idiot proof and someone will make a better idiot |
 |
|
|
|
|
|
Dynamic sql in stored proc. Dynamic data type?