How to use stored procedure parameter in sql-statement?

Hi all,

I'm trying to construct a SP like this:



CREATE PROCEDURE UpdateInvoice @companyname varchar, @docno int AS
BEGIN
DECLARE @tablename varchar
SET @tablename = '[' + @companyname + '$Inbound Purch_ Document
Header]'
IF ((SELECT COUNT([Inbound Document No_]) FROM @tablename WHERE
[Inbound Document No_] = @docno) > 1)
BEGIN
Print 'Could not find document'
END
ELSE
BEGIN
Print 'Document found'
END
END
GO



But when I parse the code, I get the following errors:

Server: Msg 137, Level 15, State 2, Procedure UpdateInvoice, Line 6
Must declare the variable '@tablename'.
Server: Msg 156, Level 15, State 1, Procedure UpdateInvoice, Line 11
Incorrect syntax near the keyword 'ELSE'.


I'm quite new to T-SQL and I can't figure out, where the error is. The
variable @tablename HAS BEEN declared (hasn't it?) and what is wrong
near ELSE? Can you please help?

/Emerik

You cannot use a variable as table name. The solution is to use dynamic
SQL. Read the following article by Erland Sommarskog for detailed
discussion:
http://www.sommarskog.se/dynamic_sql.html

Also, always specify the size of a VARCHAR variable, because when you do
not specify length it is 1 by default.

DECLARE @tablename VARCHAR(35);
SET @tablename = @companyname + '$Inbound Purch_ Document Header';

DECLARE @sql NVARCHAR(2000);

SET @sql = N'SET @cnt = (SELECT COUNT([Inbound Document No_]) FROM ' +
QUOTENAME(@tablename) +
N' WHERE [Inbound Document No_] = @docno)';

DECLARE @params NVARCHAR(50);
SET @params = N'@cnt INT OUTPUT, @docno INT';

DECLARE @cnt INT;
EXEC sp_executesql @sql, @params, @cnt OUTPUT, @docno;

IF @cnt > 1
PRINT 'Could not find document';
ELSE
PRINT 'Document found';

And the logic of the IF statement somehow does not make sense, not sure
why if count is 0 you still consider a document found, and if greater
than 1 it is not found.

--
Plamen Ratchev
http://www.SQLStudio.com

>> I'm quite new to T-SQL and I can't figure out, where the error is. The variable @tablename HAS BEEN declared (hasn't it?) and what is wrong near ELSE? Can you please help? <<

That has nothing to do with your real problem. Your design is
fundamentally wrong.

In a paper filing system, each company would be given a drawer in a
filing cabinet. In SQL, a table models a set of entities of the same
kind. There should be one and only one table for each set. Instead,
you have made a table for each company! No, wait, a table for each
"'$Inbound_Purch_Document_Header" -- even worse!!

You have a 1930's paper filing system written in SQL. This design
error will force you into dynamic SQL and worse. You need to start
over with a correct data model and schema design. After that, we can
worry about learning proper ISO-111790 naming conventions, code
formatting and so forth.

Emerik (emerik-at-emerik.dk) writes:
> CREATE PROCEDURE UpdateInvoice @companyname varchar, @docno int AS
> BEGIN
> DECLARE @tablename varchar
> SET @tablename = '[' + @companyname + '$Inbound Purch_ Document
> Header]'
> IF ((SELECT COUNT([Inbound Document No_]) FROM @tablename WHERE
> [Inbound Document No_] = @docno) > 1)
> BEGIN
> Print 'Could not find document'
> END
> ELSE
> BEGIN
> Print 'Document found'
> END
> END
> GO
>
>

Plamen gave you a solution, but I like to emphasise that there is a gross
flaw in the database design here. Companyname should be a column in the
table $Inboun Pucrh_ Document Header, and there should not be one table
per company.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Plamen,
Thanks a lot for your fast reply. It worked just perfectly. Beautiful!

> And the logic of the IF statement somehow does not make sense, not sure
> why if count is 0 you still consider a document found, and if greater
> than 1 it is not found.

Oh, yes, you're right! Actually, originally I wrote count <> 1, but I
was unsure about the syntax of "different than", so I wrote "greater
than" instead, because I had an example, where I knew, there was at
least 1 record.

> That has nothing to do with your real problem. Your design is
> fundamentally wrong.
>
> There should be one and only one table for each set. *Instead,
> you have made a table for each company! *No, wait, a table for each
> "'$Inbound_Purch_Document_Header" -- even worse!!

Hi again,
Celko and Erlang made some remarks about the "flawful" design.
Actually, I didn't design it, but I'm making an integration towards an
existing database. Surprisingly, it is a standard Navision Attain
database, that is designed in this way.

But thanks for your concern ;-)