sql server programming: Dynamic Query Syntax -- sql server programming

Re: Dynamic Query Syntax

Bil Click
5/19/2007 10:49:01 AM

Thank you Sylvain. If I use this, I get syntax error near '='

ALTER PROCEDURE [dbo].[_spInsert_output_all2]
(@outputTable nvarchar(32),
@countyname varchar(25))
AS
DECLARE @SQL Nvarchar(1000)
SET @SQL =
'INSERT INTO dbName.dbo.output_' + @outputTable + ' ( countyname )
VALUES ( @countyname)'

EXEC sp_executesql @SQL,
N'@countyname nvarchar(25)',
countyname = @countyname

[quoted text, click to view]

Re: Dynamic Query Syntax

Bil Click
5/19/2007 6:52:00 PM

Thanks to all of you, but I still cannot get complete syntax correct. My
vb.Net code repeatedly calls this sproc using different tables and
countyname. That is why I was using sp_executesql and dynamic sql.

I am still confused on how & where to declare & set the variables. If anyone
could show the complete script I would greatly appreciate it!!!

[quoted text, click to view]

Re: Dynamic Query Syntax

Sylvain Lafontaine
5/19/2007 7:25:55 PM

I mean in the following place:

SET @SQL =
'INSERT INTO dbName.dbo.output_' + @outputTable + ' ( countyname )
VALUES ( @countynameIN)'

instead of:

SET @SQL =
'INSERT INTO dbName.dbo.output_' + @outputTable + ' ( countyname )
VALUES ( @countyname)'

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

[quoted text, click to view]

Re: Dynamic Query Syntax

MH
5/19/2007 7:34:24 PM

The syntax is wrong for your SET line, try:

SET @SQL = 'INSERT INTO dbName.dbo.output_' + @outputTable + ' (countyname)
VALUES (' + @countyname + ')'

MH

[quoted text, click to view]

Re: Dynamic Query Syntax

Erland Sommarskog
5/19/2007 10:18:31 PM

Bil Click (BilClick@discussions.microsoft.com) writes:
[quoted text, click to view]

The error is one the very last line, which you would also notice if
you care to read the line number in the error message.

Besides what is the rationale of having the table name parameterised?
This is usually at odds with best practice. And, as you have noticed,
it makes programming more difficult,

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Dynamic Query Syntax

Uri Dimant
5/20/2007 12:00:00 AM

Bil

declare @sql nvarchar(1000),
@col sysname,
@orderid int

select @col='OrderID', @orderid=10248

set @sql = 'select * from Northwind..Orders where '+@col+'= @ordid'
exec sp_executesql @sql, N'@ordid int',@orderid

[quoted text, click to view]

Re: Dynamic Query Syntax

Erland Sommarskog
5/20/2007 12:00:00 AM

Bil Click (BilClick@discussions.microsoft.com) writes:
[quoted text, click to view]

But why are there multiple tables? Why not one table which is keyed
by whatever you pass in @outputTable? Therein lies your problem.

If there are just a few such tables, why not one stored procedure for each
table? If the design is really so horrible that there are many of them -
naybe it's best not to bother with stored procedures at all?

[quoted text, click to view]

Think of what you pass to sp_executesql as anonymous stored procedure.
The surrounding parameters has its set of parameters, and the inner
procedure has its set, defined by the second parameter to sp_executesql.
They could have the same name (which probably is the most common), or
they could have different name.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Dynamic Query Syntax

Bil Click
5/21/2007 8:11:06 AM

ALTER PROCEDURE [dbo].[_spInsert_output_all2]
@outputTable nvarchar(32),
@countyname nvarchar(25)
AS
DECLARE @sql nvarchar(1000),@cty nvarchar(25)
SET @cty = @countyname
SET @SQL = 'INSERT INTO dbName.dbo.output_' + @outputTable + ' ( countyname
) ' +
' VALUES (' + @cty + ') '

exec sp_executesql @sql,
@params = N'@outputTable nvarchar(32), @countyname nvarchar(25)',
@param1=@outputTable, @param2=@cty

I must have a mental block on this problem! Thought I had the code this time
but now error is "The name "Camden" is not permitted in this context".
'Camden' was input parameter value for @countyname & ''click' was
@outputTable.

Erland, there are 70 - 80 output tables and the number varies. Legacy design
that does have *some* advantages. I am trying to consolidate stored
procedures for each table into one.

Thanks again - Bil

[quoted text, click to view]

Re: Dynamic Query Syntax

Bil Click
5/21/2007 12:33:01 PM

OK, I finally got the right combination! Many Thanks again to all!

ALTER PROCEDURE [dbo].[_spInsert_output_all2]
@outputTable nvarchar(32),
@countyname nvarchar(25)
AS
DECLARE @sql nvarchar(1000)

SET @SQL = 'INSERT INTO dbo.output_' + @outputTable +
' ( countyname) ' +' VALUES (@cty) '

exec sp_executesql @sql,N'@cty nvarchar(25)',@countyname

-----------------------------------------------------------------------------
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.