Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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/

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc