473,847 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with dynamic SQL Stored Procedure

I have a stored procedure spGetAccessLogD ynamic 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 spGetAccessLogD ynamic '24', '2005/07/04 00:00:00 AM', '2005/11/04
00:00:00 AM', 'TimeAccessed DESC'
CREATE PROCEDURE spGetAccessLogD ynamic
(
@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.Ti meAccessed,
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_DateMi dnightPrevious( ''' +
@StartDate + ''' ) AND dbo.func_DateMi dnightNext( ''' + @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
2 5731
On 11 Apr 2005 08:44:24 -0700, Caro wrote:
I have a stored procedure spGetAccessLogD ynamic 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 spGetAccessLogD ynamic '24', '2005/07/04 00:00:00 AM', '2005/11/04
00:00:00 AM', 'TimeAccessed DESC'
CREATE PROCEDURE spGetAccessLogD ynamic
(
@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.Ti meAccessed,
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_DateMi dnightPrevious( ''' +
@StartDate + ''' ) AND dbo.func_DateMi dnightNext( ''' + @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 spGetAccessLogD ynamic '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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2390
by: Guinness Mann | last post by:
When you guys talk about "dynamic SQL," to what exactly are you referring? Is dynamic SQL anything that isn't a stored procedure? Specifically, I use ASP.NET to communicate with my SQL Server 2000, using an SqlConnection object to open the database and an SqlCommand object to transfer my SQL text to the database. Is this the "dynamic SQL" that is such a bad thing? What is my alternative? Wait until after my program is working and...
4
8107
by: MD | last post by:
I am trying to create a dynamic SQL statement to create a view. I have a stored procedure, which based on the parameters passed calls different stored procedures. Each of this sub stored procedure creates a string of custom SQL statement and returns this string back to the main stored procedure. This SQL statements work fine on there own. The SQL returned from the sub stored procedure are returned fine. The datatype of the variable that...
7
8409
by: Michael C# | last post by:
Is it possible to create Dynamic SQL queries in MySQL, like in SQL Server? i.e., the EXECUTE command or sp_executesql stored procedure in SQL Server. TIA
1
34981
by: Todd Peterson | last post by:
I'm a newbie to DB2 and am trying to figure out how to write a stored procedure, using dynamic SQL statements to return a result set. I believe the majority of the hurdles I have been facing might be due to the fact that the samples and postings I have read have been related to the UDB... I believe our company is on some version of MVS or OS/390, but I am not sure which one, at the time of this posting. I have an example, below, of a...
18
5993
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such as (without the quotes)... " where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...
1
7538
by: peaceburn | last post by:
Hi, I'm gonna pull my hair in the coming days with these DB2 stored procedures. So the issue, let's assume a simple stored procedure like this : CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( ) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------
7
6148
by: sri | last post by:
My environment is DB2v9.1, Windows/AIX and I am trying to compile the stored proc below and I am getting an error, "ERROR SQL0104N An unexpected token "WITH" was found following "User_ID = UsrID; ". Expected tokens may include: "SELECT". LINE NUMBER=11. SQLSTATE=42601" CREATE PROCEDURE TestProc(IN UsrID INT) LANGUAGE SQL SPECIFIC TestProc DYNAMIC RESULT SETS 1
7
3030
by: Ronald S. Cook | last post by:
I've always been taught that stored procedures are better than writing SQL in client code for a number of reasons: - runs faster as is compiled and lives on the database server - is the more proper tier to put it since is a data function But then I've heard that writing SQL in my client .NET code might run just as fast? Dynamic SQL or something? And then there's LINQ on the horizon. Is it a successor to everything
0
3198
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works fine. Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB for LUW 9.5.
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7062
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4133
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.