By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,694 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,694 IT Pros & Developers. It's quick & easy.

Help with dynamic SQL Stored Procedure

P: n/a
I have a stored procedure spGetAccessLogDynamic and when I try to call
it I get the following error:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'S'.

I dont know why because I dont have anything refering to stored
procedure 'S'

I have ran my SQL String with sample values and it works fine. So I
am presuming that it is some kind of syntax error in my stored
procedure but have tried everything and cant find it!

Anyway here is the sample data I am using to call it and my sp

Exec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/04
00:00:00 AM', 'TimeAccessed DESC'
CREATE PROCEDURE spGetAccessLogDynamic
(
@PinServiceID varchar (4),
@StartDate varchar(40),
@EndDate varchar(40),
@SortExp varchar (100)
)

AS

-- Create a variable @SQL Statement
DECLARE @SQLStatement varchar

-- Enter the Dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,
C.Forename, C.Surname
FROM AccessLog A, Members C, Pins P
WHERE P.PinValue = A.PinValue AND
P.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''
AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +
@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate
+''')
GROUP BY A.PinValue,
A.TimeAccessed, C.Forename, C.Surname
ORDER BY ' + @SortExp)

-- Execute the SQL statement
EXEC ( @SQLStatement)
GO

Any help would be very very much appreciated!!!!!!

Thanks
Caro
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 11 Apr 2005 08:44:24 -0700, Caro wrote:
I have a stored procedure spGetAccessLogDynamic and when I try to call
it I get the following error:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'S'.

I dont know why because I dont have anything refering to stored
procedure 'S'

I have ran my SQL String with sample values and it works fine. So I
am presuming that it is some kind of syntax error in my stored
procedure but have tried everything and cant find it!

Anyway here is the sample data I am using to call it and my sp

Exec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/04
00:00:00 AM', 'TimeAccessed DESC'
CREATE PROCEDURE spGetAccessLogDynamic
(
@PinServiceID varchar (4),
@StartDate varchar(40),
@EndDate varchar(40),
@SortExp varchar (100)
)

AS

-- Create a variable @SQL Statement
DECLARE @SQLStatement varchar

-- Enter the Dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,
C.Forename, C.Surname
FROM AccessLog A, Members C, Pins P
WHERE P.PinValue = A.PinValue AND
P.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''
AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +
@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate
+''')
GROUP BY A.PinValue,
A.TimeAccessed, C.Forename, C.Surname
ORDER BY ' + @SortExp)

-- Execute the SQL statement
EXEC ( @SQLStatement)
GO

Any help would be very very much appreciated!!!!!!

Thanks
Caro


Hi Caro,

1. Default length for varchar is 1. Since you didn't specify length for
@SQLStatement, it has length 1. The value of the variable is 'S' when
the EXEC statement is executed.

2. When debugging dynamic SQL, a simple method is to temporarily replace
the EXEC (@SQLStatement) statement with PRINT @SQLStatement. That allows
you to visually review the code that actually will be executed.

3. The date format you're using is ambiguous. Is 2005/07/04 april 7th or
july 4th? These are the only formats that are guaranteed to be always
interpreted correctly by SQL Server:
* yyyymmdd (for date only - time defaults to midnight. No dashes,
slashes, dots or other interpunction!!))
* yyyy-mm-ddThh:mm:ss.ttt (for date and time - the milliseconds part
is optional; the dashes, colons and capital T are required).

4. Your code might be vulnerable to SQL Injection attacks. What is the
source of the @SortExp parameter? If it's from an input box in your
application, then what would prevent a user from calling the proc with
these arguments:
EXEC spGetAccessLogDynamic '24', '20050704', '20051104',
'TimeAccessed DESC; DROP TABLE Members --'

Check out this site: http://www.sommarskog.se/dynamic_sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2

P: n/a
Thanks so much Hugo.

I now have it all up and running!!!

My SortExp is from an asp datagrid's bound columns but thanks for the
warning about injection attacks!

Thanks again

Caro
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.