Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having trouble with Stored Proc syntax 2

Status
Not open for further replies.
j0em0mma

j0em0mma

Programmer
Jul 31, 2003
131
US
I have the following stored procedure body I am trying to build in SQL Server Express:

Code: 
ALTER PROCEDURE [dbo].[sp_GetOrdersDistributed] 
	-- Add the parameters for the stored procedure here
	@FromDate DateTime, 
	@ToDate DateTime,
	@Active int,
	@SearchText varchar(3000),
	@OrderBy varchar(100),
	@OrderByType varchar(4)

AS
	Declare @strSQL varchar(4000)

@strSQL = "SELECT dbo.tbl_Items.ItemName AS [Item Name],"
@strSQL = @strSQL + " dbo.tbl_Items.ItemDescription AS [Item Description],"
@strSQL = @strSQL + " COUNT(dbo.tbl_Orders.OrderID) AS [Orders Distributed]"
@strSQL = @strSQL + " FROM dbo.tbl_OrderItems"
@strSQL = @strSQL + " INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID"
@strSQL = @strSQL + " RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID"
@strSQL = @strSQL + " WHERE	dbo.tbl_Orders.DateEntered >= " + @FromDate
@strSQL = @strSQL + " AND dbo.tbl_Orders.DateEntered <= " + @ToDate
@strSQL = @strSQL + " AND Active >= " + @Active
@strSQL = @strSQL + @SearchText
@strSQL = @strSQL + " GROUP BY dbo.tbl_Items.ItemName,"
@strSQL = @strSQL + " dbo.tbl_Items.ItemDescription"
@strSQL = @strSQL + " ORDER BY " + @OrderBy + " " + @OrderByType

EXEC(@strSQL)

When I do a check on it, I get the following errors:

Code: 
Msg 102, Level 15, State 1, Procedure sp_GetOrdersDistributed, Line 13
Incorrect syntax near '@strSQL'.
Msg 137, Level 15, State 2, Procedure sp_GetOrdersDistributed, Line 27
Must declare the scalar variable "@strSQL".

Does what I'm trying to do make sense? Can you tell me what is syntactically wrong with this (might be a lot, admittedly)
 
Sort by date Sort by votes
For starters, replace all " quotes with '.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
try this
Code: 
ALTER PROCEDURE [dbo].[sp_GetOrdersDistributed] 
    -- Add the parameters for the stored procedure here
    @FromDate DateTime, 
    @ToDate DateTime,
    @Active int,
    @SearchText varchar(3000),
    @OrderBy varchar(100),
    @OrderByType varchar(4)

AS
    Declare @strSQL varchar(4000)

select @strSQL = 'SELECT dbo.tbl_Items.ItemName AS [Item Name],'
 + ' dbo.tbl_Items.ItemDescription AS [Item Description],'
 + ' COUNT(dbo.tbl_Orders.OrderID) AS [Orders Distributed]'
 + ' FROM dbo.tbl_OrderItems'
 + ' INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID'
 + ' RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID'
 + ' WHERE    dbo.tbl_Orders.DateEntered >= ' + convert(varchar(30),@FromDate)
 + ' AND dbo.tbl_Orders.DateEntered <= ' + convert(varchar(30),@ToDate)
 + ' AND Active >= ' + convert(varchar(1),@Active)
 + @SearchText
 + ' GROUP BY dbo.tbl_Items.ItemName,'
 + ' dbo.tbl_Items.ItemDescription'
 + ' ORDER BY ' + @OrderBy + ' ' + @OrderByType

EXEC(@strSQL)

what I did was replaced the double quotes with single quotes
and also I converted the data types since everything has to be a string
you probably want to check for NULL values at the beginning

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
k, did that:

Code: 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		##### ######
-- Create date: 2/24/2006
-- Description:	Return Parameterized Orders Report
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetOrdersDistributed] 
	-- Add the parameters for the stored procedure here
	@FromDate DateTime, 
	@ToDate DateTime,
	@Active int,
	@SearchText varchar(3000),
	@OrderBy varchar(100),
	@OrderByType varchar(4)

AS
	Declare @strSQL varchar(4000)

@strSQL = 'SELECT dbo.tbl_Items.ItemName AS [Item Name],'
@strSQL = @strSQL + ' dbo.tbl_Items.ItemDescription AS [Item Description],'
@strSQL = @strSQL + ' COUNT(dbo.tbl_Orders.OrderID) AS [Orders Distributed]'
@strSQL = @strSQL + ' FROM dbo.tbl_OrderItems'
@strSQL = @strSQL + ' INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID'
@strSQL = @strSQL + ' RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID'
@strSQL = @strSQL + ' WHERE	dbo.tbl_Orders.DateEntered >= ' + @FromDate
@strSQL = @strSQL + ' AND dbo.tbl_Orders.DateEntered <= ' + @ToDate
@strSQL = @strSQL + ' AND Active >= ' + @Active
@strSQL = @strSQL + @SearchText
@strSQL = @strSQL + ' GROUP BY dbo.tbl_Items.ItemName,'
@strSQL = @strSQL + ' dbo.tbl_Items.ItemDescription'
@strSQL = @strSQL + ' ORDER BY ' + @OrderBy + ' ' + @OrderByType

EXEC(@strSQL)

Still same errors:

Code: 
Msg 102, Level 15, State 1, Procedure sp_GetOrdersDistributed, Line 19
Incorrect syntax near '@strSQL'.
Msg 137, Level 15, State 2, Procedure sp_GetOrdersDistributed, Line 33
Must declare the scalar variable "@strSQL".
 
Upvote 0 Downvote
oops, did not see your post SQLDenis, trying it now
 
Upvote 0 Downvote
And... how about some extra quotes around converted @FromDate and @ToDate?

Gotta love dynamic SQL [wink].

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
yep
Code: 
ALTER PROCEDURE [dbo].[sp_GetOrdersDistributed] 
    -- Add the parameters for the stored procedure here
    @FromDate DateTime, 
    @ToDate DateTime,
    @Active int,
    @SearchText varchar(3000),
    @OrderBy varchar(100),
    @OrderByType varchar(4)

AS
    Declare @strSQL varchar(4000)

select @strSQL = 'SELECT dbo.tbl_Items.ItemName AS [Item Name],'
 + ' dbo.tbl_Items.ItemDescription AS [Item Description],'
 + ' COUNT(dbo.tbl_Orders.OrderID) AS [Orders Distributed]'
 + ' FROM dbo.tbl_OrderItems'
 + ' INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID'
 + ' RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID'
 + ' WHERE    dbo.tbl_Orders.DateEntered >= ''' + convert(varchar(30),@FromDate) +''
 + ' AND dbo.tbl_Orders.DateEntered <= ''' + convert(varchar(30),@ToDate) +''
 + ' AND Active >= ' + convert(varchar(1),@Active)
 + @SearchText
 + ' GROUP BY dbo.tbl_Items.ItemName,'
 + ' dbo.tbl_Items.ItemDescription'
 + ' ORDER BY ' + @OrderBy + ' ' + @OrderByType

EXEC(@strSQL)

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Thank you very much for your help guys, never had to get into T-SQL like this, so it's a bit new for me.

I have altered the Stored Procedure to the following:

Code: 
ALTER PROCEDURE [dbo].[sp_GetOrdersDistributed] 
	-- Add the parameters for the stored procedure here
	@FromDate varchar(50), 
	@ToDate varchar(50),
	@Active int,
	@SearchText varchar(3000),
	@OrderBy varchar(100),
	@OrderByType varchar(4)

AS
    Declare @strSQL varchar(4000)

select @strSQL = 'SELECT dbo.tbl_Items.ItemName AS [Item Name],'
 + ' dbo.tbl_Items.ItemDescription AS [Item Description],'
 + ' COUNT(dbo.tbl_Orders.OrderID) AS [Orders Distributed]'
 + ' FROM dbo.tbl_OrderItems'
 + ' INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID'
 + ' RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID'
 + ' WHERE    dbo.tbl_Orders.DateEntered >= ''' + @FromDate +''
 + ' AND dbo.tbl_Orders.DateEntered <= ''' + @ToDate +''
 + ' AND Active >= ' + convert(varchar(1),@Active)
 + @SearchText
 + ' GROUP BY dbo.tbl_Items.ItemName,'
 + ' dbo.tbl_Items.ItemDescription'
 + ' ORDER BY ' + @OrderBy + ' ' + @OrderByType

EXEC(@strSQL)

This updates fine, no warnings. When I try to run the following:

Code: 
exec sp_GetOrdersDistributed '2005-03-13 13:59:35.903', '2006-03-13 13:59:35.903', 1, '', 'dbo.tbl_Items.ItemDescription', 'ASC'

, using dateTime stamps directly from the tbl_Orders table itself, I get the following error:

Code: 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2006'.

I know this stuff prob. seems really low level, but I really appreciate it!
 
Upvote 0 Downvote
Four quotes ('''') after dates .

And space before @SearchText won't hurt. And maybe escaped strings... I'll shut up now [noevil]

(sorry, bad day).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Thank you both very much! 4 quotes did it. Also, I did not need to run exec on the stored procedure, just inside it. Man, T-SQL error messages don't help much!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
606
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
351
gmmastros
gmmastros
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
341
gmmastros
gmmastros
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
196
Chris Miller
Chris Miller
niall5098
  • Locked
  • Question
Create plan guide with date time parameters
Replies
3
Views
121
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top