Logging Dynamic SQL Variable When EvaluateAsExpression=TRUE
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
ADVERTISEMENT
Jul 23, 2015
Where do you set an SSIS EvaluateAsExpression property of the variable to TRUE?
[URL]
View 3 Replies
View Related
Sep 28, 2007
I have a new SSIS package that I add a new string variable to with the EvaluateAsExpression property set to True. In the Properties window, I click inside the Expression property, but do not receive the Browse Button (...) to launch the expression builder window.
This appears to be specific to my machine since this same package behaves as expected on other PCs. I have tried to build my expression in a text editor and paste it into the Expression property, but only the first line of the expression is pasted. Am I missing a configuration or install somewhere? Any thoughts on solutions or work arounds will be greatly appreciated.
View 5 Replies
View Related
Jul 31, 2015
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).Â
View 2 Replies
View Related
Feb 7, 2007
Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:
I currently have logging enable on several of my packages.
However, we are still in development of our packages and are reaching upwards
of 100 and logging will eventually need to be active on all of them. In
production, there will still be a development server and a production server,
both with different server names and user id/pwd.
I am looking for a way to dynamically change the logon information for the
logging so that we do not have to have someone go through and manually change
the options. I have tried using Dynamic Properties Task, but this only works on
the 2nd run of the package.
-----
As a second question: can anyone explain to me why the errordescription field
in sysdtssteplog is cut short?
View 1 Replies
View Related
Oct 13, 2015
I would like to have a dynamic trigger on INSERT, UPDATE and DELETE. (each their own trigger).
What i want, is the trigger to log what has happened with the record. For the insert trigger, i would like to select all columns and put them in 1 column to my logging table. For the update trigger, i need the same, however for both of the old and new items.
For example:
CREATE TABLE [dbo].[TRIGGER_TEST](
[COLUMNA] [int] IDENTITY(1,1) NOT NULL,
[COLUMNB] [nvarchar](50) NOT NULL,
[COLUMNC] [nvarchar](20) NULL,
[COLUMND] [date] NULL,
[COLUME] [decimal](2, 2) NULL,
[Code] ....
When I insert an item in TRIGGER_TEST, i would like to see what is inserted in the TRIGGER_LOGGING table.
INSERT INTO [dbo].[TRIGGER_TEST]
([COLUMNB]
,[COLUMNC]
,[COLUMND]
,[COLUME])
VALUES
('test'
,'hello'
,getdate()
,0.1)
GO
I would like to see in my TRIGGER_LOGGING table in NEW:
COLUMNB='test'|COLUMNC='hello'|COLUMND='2015-10-13 16:04'|COLUME=0,10
View 0 Replies
View Related
Feb 15, 2006
Hi,
I've come up against a problem in my error handling.
I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.
I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:
Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.
Is this a bug in SSIS? Is there any sort of workround that anybody knows of?
thanks
- Jerzy
View 3 Replies
View Related
Aug 28, 2015
I have enabled SSIS logging for a Package.
Is it possible for SSIS logging to output the value of a variable.Â
Currently, it is only outputting the name of the variable, such as:"User::FilePath"
View 2 Replies
View Related
Dec 4, 2007
Hi,
A few Q's if I may:
When configuring a variable you have to set its Value, even if EvaluateAsExpression = TRUE and an expression is specified. Presumably the Value is a compile-time default which holds until runtime?
I have 2 Execute SQL tasks preceeding a Data Flow Task. Precedence constraints require both Exec SQL Tasks to have completed successfully, before the data flow task can be entered. Each Exec SQL Task sets a variable, and a 3rd variable, which is evaluated as an expression, is a function of the 1st 2. Does the expression get re-evaluated automatically+immediately, every time one or both of its dependent variables' values changes? Thus can it be GUARANTEED that the 3rd variable has indeed been re-evaluated BEFORE the data flow is entered?
Per 1000 runs I get a dozen or so of the following errors: 'expression for variable ... failed evaluation. There was an error in the expression.' Any ideas..?
View 3 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
Oct 4, 2006
Hi guys, I have the following store procedure:PROCEDURE dbo.AddSearchColumn (@A1, @A2. @A3, @A4, @A5) ASDeclare @cElements cursor,@DocNum varchar(100)BEGIN Set @cElements = cursor for select FirstNaname rom dbo.DocTable1open @cElements fetch NEXT from @cElements into @DocNumwhile (@@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 inputendENDclose @cElementsDEALLOCATE @cElements I hope my explanation is clear.Please, give some hints.Thanks for your time,Christian Malatesti
View 7 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
Jun 18, 2007
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
View 3 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
