November 25, 2008 at 7:43 pm
ALTER PROCEDURE SP_TEST
@P_TABLEN varchar(30),
@P_STARTDATE datetime,
@P_ENDDATE datetime,
@COLUMNNAME VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query varchar(1024)
DECLARE @ESQ char(1)
set @ESQ=char(39)
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+'
WHERE CONVERT(VARCHAR(10),@COLUMNNAME,101)>='
+@ESQ+CONVERT(VARCHAR(10),@P_STARTDATE,101)+@ESQ +
'and CONVERT(VARCHAR(10),@COLUMNNAME,101)<='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101)+@ESQ
EXEC (@query)
END
GO
WHEN I RUN THIS I GET FOLLOWING ERROR PLESE HELP "Must declare the scalar variable "@COLUMNNAME".
November 25, 2008 at 8:00 pm
Try replacing the @query variable with the following.
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+'
WHERE ' + @COLUMNNAME + '>='
+@ESQ+CONVERT(VARCHAR(10),@P_STARTDATE,101)+@ESQ +
' and ' + @COLUMNNAME + ' <='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101)
November 25, 2008 at 8:07 pm
Before I answer your question I have a couple of suggestions. First you should not name user stored procedures with SP as the query optimizer will first look for the object in master then in the user database. Also you need to be careful using dynamic SQL even within an SP as it invites SQL INJECTION. If you are going to use dynamic SQL you should use sp_executesql and add checks for SQL INJECTION.
Well, your error is because you have your concatentation done incorrectly so the dynamic SQL expects a parameter/variable called @COLUMNNAME. Here is what your query string evaluates to with the included call:
Exec sp_Test 'test', '1/1/08', '2/1/08', 'test'
SELECT * FROM test WHERE CONVERT(VARCHAR(10),@COLUMNNAME,101)>='01/01/2008'and CONVERT(VARCHAR(10),@COLUMNNAME,101)<='02/01/2008'
And I think you want the query string to be:
SELECT * FROM test WHERE CONVERT(VARCHAR(10),test, 101)>='01/01/2008'and CONVERT(VARCHAR(10),test,101)<='02/01/2008'
So you need to change your concatenation to:
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+
' WHERE CONVERT(VARCHAR(10),' + @COLUMNNAME + ',101)>='
+@ESQ+CONVERT(VARCHAR(10), @P_STARTDATE,101) + @ESQ
+ 'and CONVERT(VARCHAR(10),' + @COLUMNNAME+ ',101)<='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101) +@ESQ
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 8:40 pm
Dear ken:
It was very nice to meet with you today about the dynamic sql help.
I appreciate the time you took to post me. I am very interested in meeting with you in sql server central.com for you and look forward to hearing from you more.
I replaced the query which you sent but it is still not working, can you please tweak the code and post the entire code again.
Sincerely,
srathna
November 25, 2008 at 8:50 pm
Dear Jack:
It was very nice to meet with you today about the dynamic sql help.
I appreciate the time you took to post me. i tried replacing the query but still not working.Can u see the code on more time please.
ALTER PROCEDURE USP_TEST
@P_TABLEN varchar(30),@COLUMNAME VARCHAR(30),
@P_STARTDATE datetime,
@P_ENDDATE datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query varchar(1024)
DECLARE @ESQ char(1)
set @ESQ=char(39)
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+
' WHERE CONVERT(VARCHAR(10),' + @COLUMNNAME + ',101)>='
+@ESQ+CONVERT(VARCHAR(10), @P_STARTDATE,101) + @ESQ
+ 'and CONVERT(VARCHAR(10),' + @COLUMNNAME+ ',101)<='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101) +@ESQ
EXEC (@query)
END
GO
SAME ERROR
November 25, 2008 at 8:55 pm
I found a missing quote at the end. Try this.
ALTER PROCEDURE SP_TEST
@P_TABLEN varchar(30),
@P_STARTDATE datetime,
@P_ENDDATE datetime,
@COLUMNNAME VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query varchar(1024)
DECLARE @ESQ char(1)
set @ESQ=char(39)
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+'
WHERE ' + @COLUMNNAME + '>='
+@ESQ+CONVERT(VARCHAR(10),@P_STARTDATE,101)+@ESQ +
' and ' + @COLUMNNAME + ' <='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101) + ''''
EXEC (@query)
END
GO
November 25, 2008 at 9:13 pm
Ken it works 🙂 thank you very much
November 25, 2008 at 9:25 pm
Dear Ken..,
WHEN I WANT TO SEND THIS RESULTS TO CSV FILE I AM USING
THE FOLLOWING COMMAND
declare @sql varchar(8000)
select @sql = 'bcp "exec Adventureworks.SP_TEST "
queryout C:\BCP\SAS.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
BUT I CANNOT SEE ANY DATA IN THE FILE .Am i doing correct or not.
November 26, 2008 at 9:03 am
Couple of issues in the code you posted...
The stored procedure name is not fully qualified
There is a "," between -t and -T
declare @sql varchar(8000)
select @sql = 'bcp "exec Adventureworks..SP_TEST "
queryout C:\BCP\SAS.csv -c -t -T -S' + @@servername
exec master..xp_cmdshell @sql
PRINT @sql -- use the results printed for troubleshooting below
Now - if you still can't see the file being created, open a command window and paste the printed @Sql value results and execute it from there (it should be something like bcp "exec Adventureworks..SP_TEST "
queryout C:\BCP\SAS.csv -c -t -T -S YourServerNameGoesHere)
If that creates the file then the service account SQL Server is running under does not have access to create the file in the folder specified...have SQL Server run under your account and see if you can get the file created using xp_cmdshell
If that doesn't create the file - then please post the error message you are getting
November 26, 2008 at 9:54 am
Dear Friend This was the message coming up for me when i tried using
the following query.
CREATE PROCEDURE USP_MONTHLYORDERSTEST
@P_TABLEN varchar(30),
@P_STARTDATE datetime,
@P_ENDDATE datetime,
@COLUMNNAME VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query varchar(1024)
DECLARE @ESQ char(1)
set @ESQ=char(39)
SELECT @query= 'SELECT * FROM ' +@P_TABLEN+'
WHERE ' + @COLUMNNAME + '>='
+@ESQ+CONVERT(VARCHAR(10),@P_STARTDATE,101)+@ESQ +
' and ' + @COLUMNNAME + ' <='
+@ESQ+CONVERT(VARCHAR(10),@P_ENDDATE,101) + ''''
EXEC (@query)
END
GO
declare @sql varchar(8000)
select @sql = 'bcp "exec PRODUCTION_KFORCE..USP_MONTHLYORDERSTEST "
queryout K:\BCP\MONTHLYORDERS.csv -c -t -T -S' + @@servername
exec master..xp_cmdshell @sql
PRINT @sql
messages:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
November 26, 2008 at 10:26 am
Since the bcp syntax is showing up there's an error in the syntax
Give a space between -S and the @@servername...i.e.
-S ' + @@servername (note the space between S and the closing quote ')
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply