How Do I Make The Name Of A Variable Dynamic?
Oct 4, 2006
Hi guys, I have the following store procedure:
PROCEDURE dbo.AddSearchColumn (@A1, @A2. @A3, @A4, @A5) AS
Declare @cElements cursor,@DocNum varchar(100)
BEGIN
Set @cElements = cursor for select FirstNaname rom dbo.DocTable1
open @cElements
fetch NEXT from @cElements into @DocNum
while (@@FETCH_STATUS = 0)
begin
if (@DocNum==@A1 //Here is what I need to do: I want use @A1 at the first loop step, @A2 at the second, and so on
....... // @A1, @A2 are the parameters in input
end
END
close @cElements
DEALLOCATE @cElements
I hope my explanation is clear.
Please, give some hints.
Thanks for your time,
Christian Malatesti
View 7 Replies
ADVERTISEMENT
Dec 31, 2007
Hi,
In my application i need to access mutiple table. I'm writing a stored procedure
in which i need to access tables such as TB01,TB02 .. Basically TBFY
where FY is parameter.
I tried for OPENQUERY, but that needs me to add a linked server, which i don't
think is a good idea.
Can anyone suggest on how can i do so?
I'm using SqlServer2000.
Thanks.
View 6 Replies
View Related
Nov 19, 2007
Hi,
I have a requirement where i need to build the table name dynamically in the following queries.
declare @REF_DATA_TYPE nvarchar(20)
set @REF_DATA_TYPE='COUNTRY'
these are 4 cases where i need to use the table name dynamically
1. IF exists(select 1 from 'MD_REF_'+@REF_DATA_TYPE where code=@code_T)
2. Update 'MD_TB_REF_'+@REF_DATA_TYPE
3. from @ACTUAL_DATA p join 'MD_REF_'+@REF_DATA_TYPE T on T.code=P.code
4. INSERT INTO 'MD_REF_'+@REF_DATA_TYPE(Code,[Name],Description)
But i am getting error when i do this.
Please let me know what to do to solve this
Thanks in advance
View 5 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
Jul 9, 2006
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View 1 Replies
View Related
Jun 27, 2007
Hi All,
The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?
Thanks,
Manoj Kumar
View 2 Replies
View Related
Jul 17, 2015
I'm trying to run the following sql
SELECT DISTINCT
Anvendelseskoder.[Usage Code] AS [Building Code],
Anvendelseskoder.[Usage Code Value] AS [Building Description],
HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder
RIGHT JOIN HeleDanmark_DAWA
[Code] ...
It gives me the following error:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "aa.KVHx" could not be bound.
When clicked, it marks the first time i call "aa.KVHx"Â
Which I do in: "WHERE S2.Nr=2 AND s2.KVHx=aa.KVHx) AS Kode2,"
View 2 Replies
View Related
Oct 16, 2015
I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
[Code] ....
Table2: TransactionFormula
Only has 2 fields, No & Amount
the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9
Here's the example result for TransactionFormula records
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
View 5 Replies
View Related
May 22, 2015
How to make SSRS report page size dynamic, or in a way where viewer can set it to the size they want?Â
Set to 0
View 2 Replies
View Related
Apr 1, 2008
In the old DTS package, we can use Active-x script to set the variable value:
DTSGlobalVariables("MessageData").Value = "This is a test"
How do we do the same thing on the SSIS?
I am under the "control Flow" tab.
Thanks.
View 11 Replies
View Related
Jun 12, 2002
Ok, I have a table with several column all labeled, column1, column2, etc. I need to loop through them so I have this statement which will loop through and get the first value, then go to colun2, etc
declare @score int
declare @stm nchar(500)
set @score=0
While @score<=50
Begin
Set @score=@score+1
set @stm='select column' + rtrim(convert(char(2),@score))+' from tablename'
exec sp_executesql @stm
if @score>50
Break
else
COntinue
end
My question is, how can I stuff this value from the dynamic sql into a variable such as:
set @newvalue='exec sp_executesql @stm '
Thanks,
Eddie
View 2 Replies
View Related
Oct 8, 2004
Hi guys
I have a trigger which retrieves database names from a table.
I need to use this retrieved database name in another sql insert statement as a variable
e.g. set @mydbname = Select .... from.. (to get the database name)
then..
insert into @mydbname.dbo.emplTable
At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds
I need the database name as a variable because i have to write to the correct database (there are 15)
Any help appreciated
Rowan
View 2 Replies
View Related
Jun 2, 2008
Hi,
how to use a Dynamic variable on a function, to explan my self more here is a sample, we use this on SP but the function not allow executing.
DECLARE @SQL nvarchar(1000);set @sql=''
DECLARE @RESULT nVARCHAR(1000);SET @RESULT=''
DECLARE @mpq int;SET @mpq=0
DECLARE @FILENAME VARCHAR(40);SET @FILENAME='parm'
SELECT @RESULT =SCHEMA_NAME((SELECT SCHEMA_ID FROM SYS.TABLES WHERE NAME=@FILENAME))+'.'
SET @SQL=N'SELECT @mpq = CASE WHEN MPQ=1 THEN 10 WHEN MPQ=2 THEN 100 WHEN MPQ=3 THEN 1000 END FROM '+@RESULT+'PARM'
EXEC SP_EXECUTESQL @SQL,N'@mpq INT OUTPUT',@mpq OUTPUT
View 9 Replies
View Related
Jul 23, 2005
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
View 6 Replies
View Related
Aug 10, 2007
Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.
declare @TableName sysname
set @TableName = 'Customers'
delcare @Output bigint
declare @SQL varchar(max)
set @SQL = 'select top 1 RowID from ' + @TableName
select @Output =
EXEC (@SQL)
create function udf_GetDatabaseFingerPrint(
@DBID bigint
)
begin
returns bigint
as
declare @dbname sysname
, @iBig bigint
, @tSQL varchar(2000)
select @dbName = Name from Master.Dbo.Sysdatabases where DBID = @DBID
set @tSQL = 'select sum(Rows) from ' + @dbName + '.dbo.sysindexes'
set @iBig = exec(@tSQL)
return @iBig
end
View 1 Replies
View Related
May 8, 2008
Hi,
I've a roblem with table variables in dynamic sql context.
If i use it with normal sql there is of course no problem.
Simplified it looks like this:
-- Standard SQL ----------------------------------------------------------------------------------------------------------------
DECLARE @TestTable as TABLE(MyBigInt bigint NOT NULL, Myint int NOT NULL);
INSERT @TestTable SELECT 4711,10
SELECT* FROM @TestTable
-----------------------------------------------------------------------------------------------------------------------------------------
If i do the same thing in dynamic sql, i get errors.
-- Dynamic SQL ----------------------------------------------------------------------------------------------------------------
DECLARE @DynamicTestTable as Table(MyBigInt bigint NOT NULL, Myint int NOT NULL);
DECLARE @SQLString as nvarchar(max), @Parameters as nvarchar(2000);
SET @Parameters = N'@DynamicTestTableOUT as Table(MyBigInt bigint NOT NULL, Myint int NOT NULL) OUTPUT';
SET @SQLString= N'INSERT @DynamicTestTableOUT SELECT 4711,10';
EXECUTE sp_executesql @SQLString, @Parameters, @DynamicTestTableOUT = @DynamicTestTable OUTPUT ;
SELECT* FROM @DynamicTestTable;
-----------------------------------------------------------------------------------------------------------------------------------------
What is wrong with tis code? Or are table variables not supported inside dynamic SQL?
Thanks in advance
Raimund
View 3 Replies
View Related
Jun 18, 2007
I'm wondering if there's a way to pass a variable to assigning a decimal datatype;
declare @intPrecision int
set @intPrecision = 3
declare @decVariable decimal(38, @intPrecision)
I've basically been given the task by my mentor to create a script to round a decimal to a given number of decimal places.
ie; 1234.56789; 2 dp => 1234.57 and not 1234.57000
Any advice would be great.
View 1 Replies
View Related
Sep 28, 2007
I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.
Declare @l_str varchar(50),
@l_count int
Select @l_count = 1
Select @l_str = 'Week' + Convert(varchar, @l_count)
Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?
Is there any better method to compare read these 52 table variables?
Thanks in advance
View 3 Replies
View Related
May 5, 2008
hello,
I'd like to know how I can grab the output of the following code and use it as variable:
declare @sql nvarchar(25)
set @sql = 'SELECT CURRENT_TIMESTAMP'
EXEC(@sql)
Thank you.
View 2 Replies
View Related
Jul 23, 2005
I am trying to assign @sql variable to @total, where @sql is a sqlstatement stored on the database, however what I am getting is itsstring value and not its calcuation. Could anybody help?DECLARE my_cursor CURSOR FORSELECT sqlstatement from Sn_SalesReportdeclare @sql varchar(255), @total varchar(20)OPEN my_cursorFETCH NEXT FROM my_cursor INTO @sql-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGINSET @total = (@sql)print @total-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM my_cursorINTO @sqlENDCLOSE my_cursorDEALLOCATE my_cursorI tried this SET @total = EXEC (@sql), but no successRod
View 9 Replies
View Related
Jul 20, 2005
Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########
View 2 Replies
View Related
Nov 2, 2007
Is it possible in SQL 2000 to return a table variable from dynamic sql? We need to have some code that looks kind of like this:
declare @qry varchar(8000)
declare @sourcetable varchar(100) -- name of source table
declare @mytable table (ID_Num int identity(1,1), Child_Key varchar(100))
set @sourcetable = (select tablename from tblConfig where app = 1)
set @qry = 'insert into @mytable
select * from @sourcetable'
execute (@qry)
if (select count(*) from @mytable) > 0
begin
'insert into myFinalTable
select * from @mytable where blah blah blah
I can get the first part to work by declaring the table variable (mytable) in the dynamic sql, but I can't figure out how to return the table object.
View 7 Replies
View Related
Dec 13, 2007
Is this possible as given below
declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end
But its not working....
can any one help me out in this.....
Thnx
Parag
View 1 Replies
View Related
May 29, 2006
I need to set a variable in a sp using a dynamic query as such:
declare @x1 varchar(50)
declare @x2 char(10)
DECLARE @SQLString VARCHAR(500)
set @x1 = (select DRfieldname from tblJournalType where journaltypeid = 2
set @SQLstring = 'select ' + @x1 + ' from tblAssettype where assettypeid = 10'
set @x2 = EXEC (@SQLstring)
The last line above is where I am getting the error. Is this possible to do this?
Thanks for any help
smHaig
View 1 Replies
View Related
May 8, 2006
I am trying to use this statement in a variable, including another variable:
"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @[User::LastTimestamp]
But the variable value insists on giving me this error:
The expression for variable "VariableName" failed evaluation. There was an error in the expression.
I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.
Is it because the expression is part string and part integer? If so, how is that avoided?
Thanks in advance
View 2 Replies
View Related
Nov 1, 2007
declare @DatabaseName varchar(128)
set @DatabaseName = 'my new db test'
DECLARE @SQLStr varchar(500)
PRINT @DatabaseName
set @SQLStr = 'use '
+ @DatabaseName
+ ' PRINT '
+ @DatabaseName
EXEC (@SQLStr)
Error Output:
--------------------------------------------------------------------------
my new db test
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'my'. No entry found with that name. Make sure that the name is entered correctly.
-----------------------------------------------------------------------
Any idea how to get the entire database name to be used with spaces in the database name. It prints the @DatabaseName just fine, but in the dynamic sql, it truncates after the first space.
I already tried N'my new db test' in the set statement. That didn't work. I tried using NVARCHAR when declaring the variable.
Let me know as soon as you can. Thanks!!
View 7 Replies
View Related
Jan 30, 2007
Ok, so I have this stored procedure that Inserts a record and I need to return the ID inserted to pass to another procedure. I ended up having to use sp_executesql because I need to dynamically add the schema to the table. The issue that I am having is that the variable that I try to pass to the other procedure always ends up null, but it shows the correct output. Just to clarify on this, i am an Applications developer and not a DBA so I may be going about this all wrong. Any help on this would be greatly appreciated.
Here is the SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AddAnalyte]
@Analyt NVarChar(100),
@LName NVarChar(100),
@AtomicNum NVarChar(100),
@AtomicMass NVarChar(100),
@Weight NVarChar(100),
@HalfLife NVarChar(100),
@Units NVarChar(100),
@HF_Secs NVarChar(100),
@Gas NVarChar(100),
@Nat NVarChar(100)
AS
DECLARE @ID INT,@i INT
DECLARE @schema VarChar(50)
DECLARE @sql NVARCHAR(512)
BEGIN
SET @Schema = (SELECT schema_name())
SET @sql = N'INSERT INTO [' + @schema + '].[Analytes] ([Analyte],[LongName],[AnalyteType]) VALUES (''' + @Analyt + ''',''' + @LName + ''',1)';
EXEC sp_executesql @query = @sql;
SET @sql = N'SELECT MAX(AnalytePK) FROM [' + @schema + '].[Analytes]';
EXEC sp_executesql
@query = @sql,
@params = N'@ID INT OUTPUT',
@i = @ID OUTPUT
EXEC sp_AddParameterValue 6, @i, 'AtomicNum', @AtomicNum, '';
EXEC sp_AddParameterValue 6, @i, 'AtomicMass', @AtomicMass, '';
EXEC sp_AddParameterValue 6, @i, 'Weight', @Weight, '';
EXEC sp_AddParameterValue 6, @i, 'HalfLife', @HalfLife, '';
EXEC sp_AddParameterValue 6, @i, 'Units', @Units, '';
EXEC sp_AddParameterValue 6, @i, 'HF_Secs', @HF_Secs, '';
EXEC sp_AddParameterValue 6, @i, 'Gas', @Gas, '';
EXEC sp_AddParameterValue 6, @i, 'Natural', @Nat, '';
END
View 13 Replies
View Related
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
View Related
May 25, 2004
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256)
DECLARE @TargetDate smalldatetime
DECLARE @TLevel int
DECLARE @n int
SET @TargetDate = '2004-05-24'
SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' +
CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
CONVERT(varchar(3), @TLevel) + '))'
exec (@SQLCmd)
if @n > 0
print 'yep'
else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@n'.
nope
Thoughts?
View 8 Replies
View Related
Jul 20, 2005
HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
View 2 Replies
View Related
May 27, 2015
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100)
set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE]
WITH
MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE]
SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. Â It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. Â I just want a query that runs directly against the server.Â
View 3 Replies
View Related
Oct 24, 2006
I am trying to use the idea as mentioned by Jamie at:
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
which is to build dynamic SQL using a variable evaluated as an expression.
Set Expression="SELECT * FROM MyTable WHERE MyColumn = " + @[VariableContainingFilterValue]
Everything works fine. The entire package works.
My next step is to log the variable so that I know, after package execution, exactly what SQL statement the package is executing.
I tried to do it by a couple of ways in a Script task:
1) Dts.Events.FireInformation(0, String.Empty, String.Format("SQL: {0}", Dts.Variables("SourceSQL").Expression), String.Empty, 0, False)
Gives me just an expression without actually evaluating it
2) Dts.Events.FireInformation(0, String.Empty, String.Format("SQL: {0}", Dts.Variables("SourceSQL").Value), String.Empty, 0, False)
Produces an error:
The expression for variable €œSourceSQL€? failed evaluation. There was an error in the expression.
Regards,
Yitzhak
View 7 Replies
View Related
Apr 28, 2007
In one step of an SSIS package, i create an outgoing XLS filename based on the current datetime setting, a la:
"myFileName_" + (DT_WSTR,4)YEAR( getdate()) + RIGHT("0" + (DT_WSTR,2)MONTH( getdate()),2)+
RIGHT("0" + (DT_WSTR,2)DAY( getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mi", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss", getdate()),2) +".xls"
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
thx/spirits,
seth j hersh
View 3 Replies
View Related
