Runtime build sql in stored procedures with output param Q?

[Lepa]

Hi

I'm trying to make this to work and need help

Here my SP and I'm building sql with output param.

Alter PROCEDURE lpsadmin_getSBWReorderDollars
(
@out decimal(10,2) output,
@sType varchar(20),
@dSearchDateFrom datetime,
@dSearchDateTo datetime,
@sOrderType char(1)
)
AS
DECLARE @sql as nvarchar(4000)

SELECT @sql = 'SELECT @out = SUM(Price*Quantity)
FROM PortraitReOrderOrder jcpre
INNER JOIN Orders jcpor
ON OrderID = OrderID
WHERE jcpor.Archive = 0
AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),
@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),
@dSearchDateTo, 101) + ''''

IF @sOrderType <> 0
SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeName
FROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'
IF @sType = 'Active'
SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 '
IF @sType = 'Shared'
SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 '
Print @sql
EXECUTE sp_executesql @sql

It gives me an error message
Must declare the variable '@out'.

Please help

[Erland Sommarskog]

[posted and mailed, please reply in news]

Lepa (le****@netscape.net) writes:

I'm trying to make this to work and need help

Here my SP and I'm building sql with output param.

Alter PROCEDURE lpsadmin_getSBWReorderDollars
(
@out decimal(10,2) output,
@sType varchar(20),
@dSearchDateFrom datetime,
@dSearchDateTo datetime,
@sOrderType char(1)
)
AS
DECLARE @sql as nvarchar(4000)

SELECT @sql = 'SELECT @out = SUM(Price*Quantity)
FROM PortraitReOrderOrder jcpre
INNER JOIN Orders jcpor
ON OrderID = OrderID
WHERE jcpor.Archive = 0
AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),
@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),
@dSearchDateTo, 101) + ''''

IF @sOrderType <> 0
SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeName
FROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'
IF @sType = 'Active'
SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 '
IF @sType = 'Shared'
SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 '
Print @sql
EXECUTE sp_executesql @sql

It gives me an error message
Must declare the variable '@out'.

Now, think of this: you call another stored procedure to execute your
SQL. Normally, in T-SQL, can a stored procedure refer to a variable
declared in another stored procedure? So why would this be possible
here?

The whole point with sp_executesql is that you can pass parameters to
it, both input and outupt. So you don't have deal with cumbersome
conversion, but you can write it right, by means or parameters to
the dynamic SQL.

Look here, for an example:
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

1) Dynamic SQL is never used in an application. it means that the
procedure is so poorly designed OR written that you cannot figure out
what it does until run time.

2) Never put the data_type in a prefix. This is SQL and not BASIC or
FORTRAN.

3) There is no such thing as a "type_id" or "type_name"; this is basic
data modeling. Those terms are like adjectives and they need a noun
to make sense -- type of what attribute? Identifier for what entity?
Name for what entity?

4) Why use the proprietary PATINDEX() instead of the portable,
standard LIKE predicate?

5)Is the @search_type really CHAR(20)? That will hard to type
correctly!

6) The encoding scheme for types of orders is awkward. Having to pull
out substrings to get meaningful parts is usually a sign of an
overloaded code -- it measures more than one independent attribute.

7) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Here is my best guess at a re-write:

CREATE PROCEDURE GetsBWReorderDollars
( @search_type CHAR(6), -- what was used
@searchdatefrom DATETIME,
@searchdateto DATETIME,
@order_type CHAR(1))
AS
SELECT SUM (price * quantity)
FROM PortraitReorderOrders AS R1,
Orders AS O1
WHERE R1.order_id = O1.order_id
AND O1.archive = 0
AND R1.created_date BETWEEN @searchdatefrom AND @searchdateto
AND @order_type <> 0
AND CASE WHEN @search_type = 'active'
AND access_code LIKE '%SHR%'
THEN 1
WHEN @search_type = 'shared'
AND access_code NOT LIKE '%SHR%'
THEN 1 ELSE 0 END = 1
--the following predicate makes no sense due to inconsistent
AND SUBSTRING (order_type, 1, 3)
= (SELECT order_type_name
FROM OrderTypes
WHERE order_type = @order_type;