Prev: SP performance problem
Next: Please Help me with query.
From: Jack on 21 Jul 2008 16:58
Hi,
This is the final part of my previous post in the morning. I have changed
the stored procedure quite a bit. The idea is the generate a select
statement where the database needs to be chosen dynamically based on a
parameter 'site' in the procedure. The procedure looks like the following
which compiles fine:

create procedure customer_process_now

@custid varchar(50),
@site varchar(10)


as

declare @sitedbname varchar(25)
declare @sql varchar(4000)
set @sitedbname = 'ts2' + @site + '_app'

select @sql = ' select custid, sales_ytd, sales_lstyr
' + ' from @sitedbname ' + '..tblcustomer '

select @sql = @sql + ' where custid = @custid '

exec sp_executesql @custid, @site, @sql


Now if I execute the above stored procedure as the following:
customer_process_now 'c000001', 'aldkm'

I get the error as follows:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 18
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

I am not certain why the above error is showing up. Any help is appreciated
in advance.

From: Plamen on 21 Jul 2008 17:31
A good start will be to read Erland Sommarskog's article on dynamic
SQL:
http://www.sommarskog.se/dynamic_sql.html

The problems you have:

1). Look up the syntax of sp_executesql in Books Online. The first
parameter is the SQL statement. Unless you explicitly name the
parameters you have to provide them in the correct order.

2). You cannot send the database name as parameter. You have to
concatenate it in the SQL:
' + ' from ' + @sitedbname + '..tblcustomer '

Even better, use QUOTENAME: ' + ' from ' + QUOTENAME(@sitedbname) +
'..tblcustomer '

3). Going back to 1, you have to send the correct parameters to
sp_executesql
- first declare @sql as NVARCHAR not VARCHAR
- use: EXEC sp_executesql @sql, N'@custid INT', @custid = @custid

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: Jack on 21 Jul 2008 17:51
Thanks a lot Plamen. I appreciate your help along with explanation. I tried
to alter the procedure in accordance with your advise. The following is the
code. However this time it is not compiling. Could you please telll me what's
what's wrong here? After the successful compile can I use the following to
execute the stored procedure:

exec customer_process_now 'c000001', 'aldkm' . Do I need to pass the @sql
before these two parameters. Thanks

"Plamen Ratchev" wrote:

> A good start will be to read Erland Sommarskog's article on dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
>
> The problems you have:
>
> 1). Look up the syntax of sp_executesql in Books Online. The first parameter
> is the SQL statement. Unless you explicitly name the parameters you have to
> provide them in the correct order.
>
> 2). You cannot send the database name as parameter. You have to concatenate
> it in the SQL:
> ' + ' from ' + @sitedbname + '..tblcustomer '
>
> Even better, use QUOTENAME: ' + ' from ' + QUOTENAME(@sitedbname) +
> '..tblcustomer '
>
> 3). Going back to 1, you have to send the correct parameters to
> sp_executesql
> - first declare @sql as NVARCHAR not VARCHAR
> - use: EXEC sp_executesql @sql, N'@custid INT', @custid = @custid
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>
From: Jack on 21 Jul 2008 17:56
Sorry here is the add-on I missed in the post:
alter procedure customer_process_now

@custid Nvarchar(50),
@site Nvarchar(10)


as

declare @sitedbname Nvarchar(25)
declare @sql Nvarchar(4000)
set @sitedbname = 'ts2' + @site + '_app'

select @sql = ' select custid, sales_ytd, sales_lstyr
' + 'from ' + @sitedbname + '..tblcustomer '
select @sql = @sql + ' where custid = @custid '
exec sp_executesql @sql, N'custid Nvarchar(50), N'site Nvarchar(10),
@custid = @custid, @site = @site

"Jack" wrote:

> Thanks a lot Plamen. I appreciate your help along with explanation. I tried
> to alter the procedure in accordance with your advise. The following is the
> code. However this time it is not compiling. Could you please telll me what's
> what's wrong here? After the successful compile can I use the following to
> execute the stored procedure:
>
> exec customer_process_now 'c000001', 'aldkm' . Do I need to pass the @sql
> before these two parameters. Thanks
>
> "Plamen Ratchev" wrote:
>
> > A good start will be to read Erland Sommarskog's article on dynamic SQL:
> > http://www.sommarskog.se/dynamic_sql.html
> >
> > The problems you have:
> >
> > 1). Look up the syntax of sp_executesql in Books Online. The first parameter
> > is the SQL statement. Unless you explicitly name the parameters you have to
> > provide them in the correct order.
> >
> > 2). You cannot send the database name as parameter. You have to concatenate
> > it in the SQL:
> > ' + ' from ' + @sitedbname + '..tblcustomer '
> >
> > Even better, use QUOTENAME: ' + ' from ' + QUOTENAME(@sitedbname) +
> > '..tblcustomer '
> >
> > 3). Going back to 1, you have to send the correct parameters to
> > sp_executesql
> > - first declare @sql as NVARCHAR not VARCHAR
> > - use: EXEC sp_executesql @sql, N'@custid INT', @custid = @custid
> >
> > HTH,
> >
> > Plamen Ratchev
> > http://www.SQLStudio.com
> >
From: Erland Sommarskog on 21 Jul 2008 18:51
Jack (Jack(a)discussions.microsoft.com) writes:
> Sorry here is the add-on I missed in the post:
> alter procedure customer_process_now
>
> @custid Nvarchar(50),
> @site Nvarchar(10)
>
>
> as
>
> declare @sitedbname Nvarchar(25)
> declare @sql Nvarchar(4000)
> set @sitedbname = 'ts2' + @site + '_app'
>
> select @sql = ' select custid, sales_ytd, sales_lstyr
> ' + 'from ' + @sitedbname + '..tblcustomer '
> select @sql = @sql + ' where custid = @custid '
> exec sp_executesql @sql, N'custid Nvarchar(50), N'site Nvarchar(10),
> @custid = @custid, @site = @site

Right now you have:

exec sp_executesql @sql, N'custid Nvarchar(50), N'site Nvarchar(10),
@custid = @custid, @site = @site

If pick this apart, we can start with:

exec sp_executesql @sql, N'

OK so far.

custid Nvarchar(50), N'

But now there are two errors. You have left out the @ for the parameter.
Parameters and variables in T-SQL always start with @. The second error
is that there is a stray string delimiter, so that the parameter-list
parameter reads:

custid Nvarchar(50), N

Which is just illegal syntax. We continue to look at your call:

site Nvarchar(10),

Since the parameter-list parameter has ended, the next token should be
a command, but it isn't so this is a syntax error. But there is no @site
parameter in your SQL string, so just remove this.

@custid = @custid, @site = @site

Syntactially we back on track here, but again the @site parameter is
useless.





--
Erland Sommarskog, SQL Server MVP, esquel(a)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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 |  Next  |  Last
Pages: 1 2 3
Prev: SP performance problem
Next: Please Help me with query.