SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert into Table-Valued-Parameter in an SP		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
129 Posts
Posted - 11/06/2012 :  08:16:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 11/06/2012 :  08:23:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Why not use a temp table or just return the rowset and insert from the client

insert @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.
Go to Top of Page

token
Posting Yak Master

United Kingdom
129 Posts
Posted - 11/06/2012 :  08:56:21  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 11/06/2012 :  12:18:50  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

token
Posting Yak Master

United Kingdom
129 Posts
Posted - 11/06/2012 :  17:21:21  Show Profile  Reply with Quote
You are right, the temp table solution was more elegant so I went for that instead. Thanks nigelrivett and visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 11/07/2012 :  09:28:13  Show Profile  Reply with Quote
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 problem

you're welcome

Glad that you got it sorted out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000