Running an INSERT statement a @variable without using EXEC

The Code-Piece below does not work because @.Tablename is not an objekt.

=============================================

DECLARE @.Tablename varchar(256)

set @.Tablename = 'MyTable'

insert into @.Tablename default values

=============================================

How can i make it work without using EXEC?

Thanks in advance

Raimund

You have to use dynamic SQL (which is what I presume you mean by using EXEC). I don't think there is any other way.|||If I use scope_identity with an insert-statement by dynamic SQL, scope_identity reurns NULL;|||

If you put the Scope_identity call into the dynamic sql you will be able to capture it to a variable. That variable can then be returned if you use the sp_executesql form of dynamic sql.

|||

You need to call the identity in the dynamic SQL

declare @.tablename varchar(100)

declare @.sql nvarchar(1000)

declare @.i int

set @.tablename = 'mytable'

set @.sql = 'insert into ' + @.tablename + ' default values

set @.i = scope_identity()'

exec sp_executesql @.sql, N'@.i int output', @.i output

select @.i

|||This does mean you can put this code in a UDF|||

Ok. It works fine.

Thanks.

Best Regards

Raimund

Running a proc. on a certain date help?

DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 3

SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber

I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current dateUse SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) to schedule it ?

-PatP|||DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = day(GetDate())
If @.returnDay = 3
begin
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
endsql