SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trying to use variable in SQL statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itmaster
Starting Member

25 Posts

Posted - 08/04/2014 :  11:52:12  Show Profile  Reply with Quote
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

USA
37133 Posts

Posted - 08/04/2014 :  12:15:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

itmaster
Starting Member

25 Posts

Posted - 08/04/2014 :  13:08:09  Show Profile  Reply with Quote
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
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 08/04/2014 :  13:17:38  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000