Hello,

I have the below but it is not working. Can anyone chime in and let me know what I am doing wrong ?

Thank you

declare @serv     varchar (15)
    ,   @str	   varchar(max)
    ,   @rundat   date		  = '2015-07-15'
    ,   @endcycle date		  = '2015-08-01'

set @serv	  = '[ServerName]'
set @str	  = 'select *
		     from ' + @serv + '.[DBName].[SchemaName].[TableName] 
			where [Process_Begin] between cast(' + ''''+ @rundat + '''' + ' as varchar) and cast(' + '''' + @endcycle + '''' + ' as varchar)'
print @str
exec @str

You are concatenating date and varchar which are not compatible. Cast the date to varchar in order to concatenate them.

set @str	  = 'select *
		     from ' + @serv + '.[DBName].[SchemaName].[TableName] 
			where [Process_Begin] between cast(' + ''''+ convert(varchar(8), @rundat, 112) + '''' + ' as varchar) and cast(' + '''' + convert(varchar(8), @endcycle, 112) + '''' + ' as varchar)';

You tried to get it right, but you're casting inside the string, instead of on the variable:

declare @serv     varchar (15)
    ,   @str	   varchar(max)
    ,   @rundat   date		  = '2015-07-15'
    ,   @endcycle date		  = '2015-08-01'

set @serv	  = '[ServerName]'
set @str	  = 'select *
		     from ' + @serv + '.[DBName].[SchemaName].[TableName] 
			where [Process_Begin] between ' + ''''+ cast(@rundat as varchar) + '''' + '  and ' + '''' + cast(@endcycle as varchar) + '''' + ' '
print @str

Thanks you.

You guys are awesome !

Guys,

When I execute it, I get the below:

select *
    from [ServerName].[DBName].[SchemaName].[TableName] 
where [Process_Begin] between '2015-07-15'  and '2015-08-01' 
Msg 203, Level 16, State 2, Line 11
The name 'select *
    from [ServerName].[DBName].[SchemaName].[TableName] 
where [Process_Begin] between '2015-07-15'  and '2015-08-01' ' is not a valid identifier.

Any ideas as to why this would occur? Of course I am replacing server, DB, schema and table name with their respective values

Thank you for your feedback on this

• Edited by Tshindaye 14 hours 42 minutes ago

use 

EXEC sp_executeSQL @str

Guys,

When I execute it, I get the below:

select *
    from [ServerName].[DBName].[SchemaName].[TableName] 
where [Process_Begin] between '2015-07-15'  and '2015-08-01' 
Msg 203, Level 16, State 2, Line 11
The name 'select *
    from [ServerName].[DBName].[SchemaName].[TableName] 
where [Process_Begin] between '2015-07-15'  and '2015-08-01' ' is not a valid identifier.

Any ideas as to why this would occur? Of course I am replacing server, DB, schema and table name with their respective values

Thank you for your feedback on this

• Edited by Tshindaye Wednesday, July 15, 2015 4:25 PM

Hello Patrick,

I am getting the following error message:

SELECT *
    FROM ServerName.DBName.SchemaName.TableName 
WHERE [Process_Begin] between '2015-07-15' and '2015-08-01'
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 33
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

What am I missing ?

Thank you

Declare your @str variable as nvarchar(max) instead of varchar(max).

Also, I would rather use parameters directly as parameters instead of embedding them into dynamic SQL.

That did it !

Thanks a lot Naomi.

I have the below but it is not working. Can anyone chime in and let me know what I am doing wrong ?

Everything? There is not much that is right your post.

 declare @serv     varchar (15)
     ,   @str       nvarchar(max)
     ,   @rundat   date            = '2015-07-15'
     ,   @endcycle date            = '2015-08-01'

 set @serv       = 'ServerName'
 set @str       = 'select *
             from ' + quotename(@serv) + '[DBName].[SchemaName].[TableName]
             where [Process_Begin] between @rundat AND @endcycle'
 print @str
 sp_executesql @str, N'@rundat date, @endcycle date', @rundate, @endcycle

Did you see how much simpler your got when you use parameterised SQL. No need to get lost in a myriad of single quotes.

Eric,

I appreciate your feedback but something is not working with your script. I am getting the error message below:

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@rundate".

Guidance that others have provided seems to work fine.

If you feel the need to tackle this problem, I'd be happy to try your fix.

Thank you

Eric,

I appreciate your feedback but something is not working with your script. I am getting the error message below:

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@rundate".

Guidance that others have provided seems to work fine.

If you feel the need to tackle this problem, I'd be happy to try your fix.

Thank you

• Marked as answer by Tshindaye Thursday, July 16, 2015 7:02 PM
• Unmarked as answer by Tshindaye Thursday, July 16, 2015 7:02 PM

Eric,

Eric?

I appreciate your feedback but something is not working with your script. I am getting the error message below:

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@rundate".

Eh, Tshindaye, excuse me, but are you trying to pull my legs? Or do you really think that @rundate and @rundat would be two different variables? If you can mangle my name with quite a few letters, may have I have the liberty to mangle one of your variable names with a single letter?

My attitude to these forums have always been that I don't feel like spoonfeeding people a solution to certain problem, but rather giving them something they can learn from and use again in the future. For this reason, I also expect people to look at the solutions from a wider perspective. That is, they don't use embrace the solution blindly, but they look at it, take it a part, until they understand how it works. In such a process, fixing simple typos like this one comes out automatically.

If you have never encountered sp_executesql before, I have more information about it in my article on dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html

Erland,

I apologize for everything and I totally agree with you. 

Going forward, I will make sure I double check prior to responding.

Patrick, you are amazing!  You too Naomi!!!