December 13, 2007 at 8:45 am
ALTER Procedure dbo.showjobs
@dbname varchar(20),
@beginDate datetime,
@EndDate datetime,
@emp varchar(4)
as
begin
set nocount on
declare @SQLCmd varchar(5000)
set @SQLCmd = 'use ' + @DBName + ';
set @beginDate = (Select changedate(@beginDate))
set @EndDate = (Select changedate(@EndDate))
if lower(''@emp'') = ''all'' set @emp= null
select empid,jobid,empname,jobname from emp
where jobDate between ''@beginDate'' and ''@EndDate''
and emp = isNull(''@emp'',emp)
'
exec (@SQLCmd)
end
when I run above proc I get errors like
Must declare the variable '@beginDate'.
Must declare the variable '@EndDate'.
Must declare the variable '@emp.
I tried many ways to convert into dynamic sql by quotes inside the proc but still not working
December 13, 2007 at 10:03 am
Your not constructing your dynamic sql correctly.
if you want to pass parameters into dynamic sql, then you MUST use sp_executesql. the EXEC function does not except parameters.
If you do not want to pass in parameters then exec is fine
your trouble exists areound concatenating your string.
declare
@dbname varchar(20),
@beginDate datetime,
@EndDate datetime,
@emp varchar(4)
set @BeginDate = '12/5/2007'
set @EndDate = '12/15/2007'
set @Emp = 'abcd'
set @dbName= 'SomeDB'
begin
set nocount on
declare @SQLCmd Nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
set @beginDate = (Select changedate(@beginDate))
set @EndDate = (Select changedate(@EndDate))
if lower(@emp) = ''all'' set @emp= null
select empid,jobid,empname,jobname from emp
where jobDate between @beginDate and @EndDate
and emp = isNull(@emp,emp)
'
-- always debug using a select, or print command.
select (@SQLCmd)
-- because you are setting the @emp variable you have to use sp_executesql
exec sp_executesql(@SQLCmd ,
'@beginDate datetime,
@EndDate datetime,
@emp varchar(4)', @BeginDate, @EndDate, @emp)
end
December 13, 2007 at 10:22 am
Could you explain this for me in detail, why do you use parameters double time there. I tried this it gives syntax error
exec sp_executesql(@SQLCmd ,
'@beginDate datetime,
@EndDate datetime,
@emp varchar(4)', @BeginDate, @EndDate, @emp)
December 13, 2007 at 10:32 am
THank yuou very much Ray
I cud do that but am getting another error
Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.
December 13, 2007 at 10:44 am
can you post the string that is being executed?
December 13, 2007 at 10:50 am
Mike Levan (12/13/2007)
THank yuou very much RayI cud do that but am getting another error
Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.
try
SET @BeginDate = '20071205'
SET @EndDate = '20071215'
Vasc
December 17, 2007 at 8:01 am
As I made this proc dynamic by passing dbname as parameter so that i can run this proc in any db, can i also do the same for views.
I want save all my views in one db and run them in multiple db's( all db's are same but named differently)
December 18, 2007 at 7:31 am
Ray M (12/13/2007)
Your not constructing your dynamic sql correctly.if you want to pass parameters into dynamic sql, then you MUST use sp_executesql. the EXEC function does not except parameters.
If you do not want to pass in parameters then exec is fine
your trouble exists areound concatenating your string.
declare
@dbname varchar(20),
@beginDate datetime,
@EndDate datetime,
@emp varchar(4)
set @BeginDate = '12/5/2007'
set @EndDate = '12/15/2007'
set @Emp = 'abcd'
set @dbName= 'SomeDB'
begin
set nocount on
declare @SQLCmd Nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
set @beginDate = (Select changedate(@beginDate))
set @EndDate = (Select changedate(@EndDate))
if lower(@emp) = ''all'' set @emp= null
select empid,jobid,empname,jobname from emp
where jobDate between @beginDate and @EndDate
and emp = isNull(@emp,emp)
'
-- always debug using a select, or print command.
select (@SQLCmd)
-- because you are setting the @emp variable you have to use sp_executesql
exec sp_executesql(@SQLCmd ,
'@beginDate datetime,
@EndDate datetime,
@emp varchar(4)', @BeginDate, @EndDate, @emp)
end
Ray M
I have a problem with this when using nvarchar, my string is more than 4000 charachters and it gives me an error. Do i need to use only nvarchar when using sp_Executesql?
December 18, 2007 at 8:31 am
If I break my dynamic sql and then concatinate it, like
@sqldcmd1 + @sqlcmd2, how am i going to use
exec sp_executesql @SQLCmd1,N'@Col datetime',@Col
December 18, 2007 at 8:55 am
You better post the error that your getting (doubt that your string is longer than 4k nchars)
DECLARE @beginDate datetime
DECLARE @EndDate datetime
DECLARE @emp varchar(4)
DECLARE @dbName sysname
SET DATEFORMAT MDY
SET NOCOUNT ON
SET @BeginDate = '12/5/2007'
SET @EndDate = '12/15/2007'
SET @Emp = 'abcd'
SET @dbName= 'SomeDB'
DECLARE @SQLCmd nvarchar(4000)
SET @SQLCmd = N'USE ' + @DBName + N';
SET @beginDate = (SELECT changedate(@beginDate))
SET @EndDate = (SELECT changedate(@EndDate))
IF LOWER(@emp) = ''all'' SET @emp= null
SELECT empid,jobid,empname,jobname
FROM emp
WHERE jobDate BETWEEN @beginDate AND @EndDate
AND emp = ISNULL(@emp,emp)
'
SELECT @SQLCmd
SELECT LEN(@SQLCmd) SQLCmdLenght
EXEC sp_executesql @SQLCmd , N'@beginDate datetime, @EndDate datetime, @emp varchar(4)', @BeginDate, @EndDate, @emp
--Tested and it works K no problems with length barerly 277Chars BUT I guess your QA is setted to MaxChars/Line of 256 chars
Vasc
December 18, 2007 at 9:08 am
yeah my length is more than 4k and so iwant to do some type break and concatinate.
December 18, 2007 at 9:12 am
if you want to pass parameters into dynamic sql, then you MUST use sp_executesql.
I guess that depends on what you're calling a "parameter"...
Mike, going back to your original post, the best thing to do is to remove as much as possible from the Dynamic SQL... only include what absolutely must be dynamic and life get's easier.
I haven't tested it because I don't have your tables or data, but this should probably work just fine...
ALTER PROCEDURE dbo.showjobs
@DBName VARCHAR(20),
@BeginDate DATETIME,
@EndDate DATETIME,
@Emp VARCHAR(4)
AS
SET NOCOUNT ON
DECLARE @SQLCmd VARCHAR(8000)
SELECT @BeginDate = (SELECT ChangeDate(@BeginDate)),
@EndDate = (SELECT ChangeDate(@EndDate)),
@Emp = NULLIF(@Emp,'ALL')
SET @SQLCmd = '
USE ' + @DBName + '
SELECT EmpID, JobID, EmpName, JobName
FROM Emp
WHERE JobDate BETWEEN ''' + @beginDate + ''' AND ''' + @EndDate + '''
AND Emp = ISNULL(''' + @emp + ''',Emp)
'
EXEC (@SQLCmd)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2007 at 9:18 am
Ray M
I have a problem with this when using nvarchar, my string is more than 4000 charachters and it gives me an error. Do i need to use only nvarchar when using sp_Executesql?
Assuming you have SQL Server 2005 have you tried nvarchar(max)?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 18, 2007 at 9:18 am
DECLARE @beginDate varchar(30)
DECLARE @EndDate varchar(30)
DECLARE @emp varchar(4)
DECLARE @dbName sysname
SET DATEFORMAT MDY
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET @beginDate = '12/5/2007'
SET @endDate = '12/15/2007'
SET @Emp = 'abcd'
SET @dbName= 'BO2'
DECLARE @SQLCmd nvarchar(4000)
SET @SQLCmd = N'USE ' + @DBName + N';
SET DATEFORMAT MDY
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @beginDate datetime
DECLARE @EndDate datetime
SET @beginDate = (SELECT dbo.ChangeDate("'+@beginDate+'"))
SET @endDate = (SELECT dbo.ChangeDate("'+@EndDate+'"))
SELECT empID,jobID,empName,jobName
FROM emp
WHERE jobDate BETWEEN @beginDate AND @endDate
AND (emp ="'+@emp+'" OR "all"="'+LOWER(@emp)+'")
'
SELECT @SQLCmd
SELECT LEN(@SQLCmd) SQLCmdLenght
DECLARE @SQLCmd1 NVARCHAR(4000)
SET @SQLCmd1=N'
SELECT "can t pass parameters"'
EXECUTE (@SQLCmd+@SQLCmd1)
Vasc
December 18, 2007 at 9:34 am
tim ..if i had sql 2005, then it wud work for max length but am using sql 2000.
jeff ...there is no other go, i want ot use only dynamic sql
Vasc ... i cant use EXEC (@sqlcmd1+@sqlcmd2) bcoz am using parameter and so have to go with
exec sp_executesql @SQLCmd1,N'@col1 datetime',@col1
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply