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 |
|
daman
Yak Posting Veteran
72 Posts |
 Posted - 2007-11-19 : 18:49:16
|
| I'd like to get input from helpful members here about the stored procedures I'm writing.1) Stored procedure that takes either ID or Name as input so I can call EXEC usp_get_values '1234' or EXEC usp_get_values 'John Smith'CREATE PROCEDURE [dbo].[usp_get_values]@Id INT = null,@Name VARCHAR(100) = nullAsSelect value from Table where Id=@Id or Name=@NameThe way it is written now, I have to enter both2) Stored procedure that takes an array as inputMy input will be an array of id. How do I declare my SP ? |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-11-19 : 22:53:19
|
| Exec [dbo].[usp_get_values] @ID = 1Exec [dbo].[usp_get_values] @Name = 'john'In sql there is really no way to pass an array. A work around is to add a table to sql that has all your id and name values in it, then code the store procedure to referance the table for the array list.Another alternative is you can always pass a string containing the sql, but that is not very safe.i.e.declare @strSql varchar(200),@Name varchar(200),@ID varchar(200)set @strSql = 'Select value from Table'set @name = '''John'',''Joe'',''tom'''set @ID = '1,2,3,4,5'exec (@StrSql + ' Where ID in (' + @ID +') and Name in (' + @Name + ')') |
 |
|
|
daman
Yak Posting Veteran
72 Posts |
Posted - 2007-11-19 : 23:10:34
|
| hi Vinnie,thanks for the input. i read somewhere that you can use xml input in sql 2005 to read in a whole list at once. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-20 : 01:35:56
|
| www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Stored procedure declaration