Trying to use variable in SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to use variable in SQL statement

Author

Topic

itmaster
Starting Member

28 Posts

Posted - 2014-08-04 : 11:52:12

Hi,
I am trying to use a variable in a sql statement, but it keeps commng back that "the Must declare the variable '@TbHold'"
and it is on top.
What I have to do is concatinate a date(moth with a year with the table name).
any ideas would be reeally appreciated.
Thank you

My code:

DECLARE @dt3 varchar(4)
DECLARE @dt varchar(2)
SET @dt = DATEPART(MONTH, GETDATE()) 

DECLARE @dt2 varchar (4)
SET @dt2 = DATEPART(YEAR, GETDATE()) 

IF LEN(@dt) < 2
BEGIN
    SET @dt = '0' + @dt 
END

SET @dt3 = RIGHT(@dt2,2) + @dt

DECLARE @@TbHold varchar(8) 
Set @TbHold = 'RAP' + @dt3 

SELECT RACUST,
       RAACCT,
       RADATE, 
       RATIME, 
       RAOPHN, 
       RACITY, 
       RASTAT, 
       RATYPE, 
       RADUR, 
       RATREV, 
       RAREV, 
       RAAUTH, 
       BillName,
       CASE WHEN RATYPE IN ('8DI','8GI','8I','8X','8D') 
           THEN raauth 
       ELSE RAPHN 
       END AS RAPHN,RADATETIME 
FROM @TbHold

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-04 : 12:15:35

You will need to use dynamic SQL for that. Please read this in its entirety to understand the security, performance and design implications: http://www.sommarskog.se/dynamic_sql.html

You should reconsider the database design.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

itmaster
Starting Member

28 Posts

Posted - 2014-08-04 : 13:08:09

Hi,
Thanks for the reply and that is a godd articale.
Unfortunatly I cannot get a ne database design they are not going to allow that.
I thought of using dynamic sql, and while I have done some dynamic sql before, I am not sure how to do this here in the from statement.
Would you be able to help with that?
Thank you

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-04 : 13:17:38

DECLARE @dt3 varchar(4)
DECLARE @dt varchar(2)
SET @dt = DATEPART(MONTH, GETDATE())

DECLARE @dt2 varchar (4)
SET @dt2 = DATEPART(YEAR, GETDATE())

IF LEN(@dt) < 2
BEGIN
SET @dt = '0' + @dt
END

SET @dt3 = RIGHT(@dt2,2) + @dt

DECLARE @TbHold varchar(8)
SET @TbHold = 'RAP' + @dt3

DECLARE @strSQL nvarchar(2000) = 'SELECT RACUST,
RAACCT,
RADATE,
RATIME,
RAOPHN,
RACITY,
RASTAT,
RATYPE,
RADUR,
RATREV,
RAREV,
RAAUTH,
BillName,
CASE WHEN RATYPE IN (''8DI'',''8GI'',''8I'',''8X'',''8D'')
THEN raauth
ELSE RAPHN
END AS RAPHN,RADATETIME
FROM '

SELECT @strSQL = @strSQL + @TbHold

SELECT @strSQL

EXEC Sp_executeSql @strSQL

If you are going to use dynamic sql , just make your code as tight as possible.

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources