Sql Server Programming :: simple sp


	simple sp_executesql question Hi all, Can I pass table variables into sp_executesql ? I don't believe t-sql supports such a thing as reference/pointer vars so I can understand why this won't work: DECLARE @ResultSet TABLE(cols) --Table full of records to be excluded DECLARE @SqlStr nvarchar(100) --Ad hoc sql str SET @SqlStr = N'SELECT * FROM [Table] WHERE NOT IN(SELECT * FROM @ResultSet)' DECLARE @Params nvarchar(50) SET @Params = '@ResultSet TABLE' --Any variation here throws syntax err --Pass table into params somehow ? EXEC sp_executesql @SqlStr, @Params, @ResultSet = --table var or select I looked up the docs and I can get it to work fine with any other data type. Kinda hoping there's some undocumented syntax for this particular situation. :) ... To work around I'm declaring and populating the @ResultSet table variable inside @SqlStr's statement which works fine, it's just not as flexible for my situation (wanted @ResultSet to be local to starting batch for further processing down the line...) Anyway, TIA experts! -Mike Erland Sommarskog wrote a very informative article on that very subject: http://www.sommarskog.se/share_data.html ML --- http://milambda.blogspot.com/ -----------------------------------------------Reply----------------------------------------------- "Mike Mertes" <mike_@hotmail.com> wrote in message news:Xns993FAB1B0F59Dmikewmhotmailcom@207.46.248.16... > Can I pass table variables into sp_executesql ? You should explain the difference between a table variable and a (sql) table. I am having a very hard time trying to show sql land the hugh difference :( :) http://beyondsql.blogspot.com -----------------------------------------------Reply----------------------------------------------- Yeah, perhaps you should just... stop. ML --- http://milambda.blogspot.com/ -----------------------------------------------Reply----------------------------------------------- "ML" <M@discussions.microsoft.com> wrote in message news:E85FB1F1-D6FB-4B26-AA9D-9061E43978BC@microsoft.com... > Yeah, perhaps you should just... stop. I guess I lost a fan but found a spine :) -----------------------------------------------Reply----------------------------------------------- =?Utf-8?B?TUw=?= <M@discussions.microsoft.com> wrote in news:E4909850- 2FF1-4064-A03E-83711C190@microsoft.com: > Erland Sommarskog wrote a very informative article on that very subject: > http://www.sommarskog.se/share_data.html > ML > --- > http://milambda.blogspot.com/ Thanks for the link :) Appreciated -----------------------------------------------Reply----------------------------------------------- =?Utf-8?B?TUw=?= <M@discussions.microsoft.com> wrote in news:E4909850- 2FF1-4064-A03E-83711C190@microsoft.com: > Erland Sommarskog wrote a very informative article on that very subject: > http://www.sommarskog.se/share_data.html > ML > --- > http://milambda.blogspot.com/ Funny, I think this is actually a more appropriate article for me: http://www.sommarskog.se/dynamic_sql.html I was using sp_executesql for that reason in the first place. There are great articles here, really thanks. -Mike -----------------------------------------------Reply----------------------------------------------- You'll find that all the articles on that site are vey well worth looking into. ML --- http://milambda.blogspot.com/ -----------------------------------------------Reply----------------------------------------------- :) This particular fan is interested in argumentation. ML --- http://milambda.blogspot.com/

Sql Server Programming

simple sp_executesql question