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 |
|
token
Posting Yak Master
133 Posts |
 Posted - 2012-11-06 : 08:16:16
|
I have declared a variable that has a table type as such:DECLARE @MyTable GenderTableType It has only one colum called 'Gender'. I want to insert into the new table type paramter values but from within a stored procedure as such:INSERT INTO @MyTable(Gender)VALUES ('M'), ('F')But I can't do this within a stored procedure because the in the declaration part it wants table type parameters to be READONLY. How do I go about inserting values from within a stored prodecure? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-06 : 08:23:59
|
| Why not use a temp table or just return the rowset and insert from the clientinsert @MyTable (Gender)exec mysp==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
token
Posting Yak Master
133 Posts |
Posted - 2012-11-06 : 08:56:21
|
| A variable in SQL called @myString is being set as 'A, B, C, D' by the front-end application.I then parse and split this string out and insert the individual characters into my @MyTable table type variable. This is used in another stored procedure that has a "SELECT * FROM SomeTable WHERE Column1 IN (SELECT <<values from table type variable>>)" statement.I don't think I can do it with a TempTable? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 12:18:50
|
quote:
Originally posted by token
A variable in SQL called @myString is being set as 'A, B, C, D' by the front-end application.I then parse and split this string out and insert the individual characters into my @MyTable table type variable. This is used in another stored procedure that has a "SELECT * FROM SomeTable WHERE Column1 IN (SELECT <<values from table type variable>>)" statement.I don't think I can do it with a TempTable?
whats the need of intermediate procedure and table valued parameter here? why cant string be passed as is to second procedure and string parsing being done inline in it populating a temporary table as Fred suggested?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2012-11-06 : 17:21:21
|
| You are right, the temp table solution was more elegant so I went for that instead. Thanks nigelrivett and visakh16 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-07 : 09:28:13
|
quote:
Originally posted by token
You are right, the temp table solution was more elegant so I went for that instead. Thanks nigelrivett and visakh16
No problemyou're welcomeGlad that you got it sorted out!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Insert into Table-Valued-Parameter in an SP