SQL Server 2012 :: Table Variable In Dynamic Query?

Jul 2, 2015

I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.

For Eg- @TableVariable

SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '

BUt it gives an error that @TableVariable must be declared

View 8 Replies


ADVERTISEMENT

SQL Server 2012 :: Dynamic Query On DB Table Names

Mar 9, 2015

I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.

declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

SQL Server 2012 :: Dynamic Query To Print Out Resultset From A Table?

Sep 9, 2015

I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation

Is there any way to print all the 3 INSERT stmt.

IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'

[code]....

View 4 Replies View Related

SQL Server 2012 :: Update Table Using Variable From Another Query?

Apr 3, 2014

I want to update one table with the value from variable using a set based approach.

so the line

>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))

works fine but I want to take @list and use it to update my customers table. here is the full code.

create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history

[code]....

View 5 Replies View Related

SQL Server 2012 :: Where Clause In Dynamic Query?

Jul 2, 2015

I am creating a dynamic query where i am appending a where clause something like -

IF (@CurCaptureDate IS NOT NULL)
SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate

PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime

but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.

i am not able to use convert here with these quotes.

i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )

but it makes it to -

AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.

View 4 Replies View Related

SQL 2012 :: Dynamic Partitioning Of Table Server

Dec 3, 2014

I need Dynamic Partition of SQL Table.

1. What is the best practice for partitioning (on date column)

2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data

3. Is creating partition occupies more disk space?

4. Is every partition would need index?

View 7 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related

SQL 2012 :: SSIS / Dynamic Updating Of A Recordset Variable?

Jul 8, 2015

updating a recordset contained in an System.Object variable during runtime.

I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.

Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.

My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?

View 5 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

SQL Server 2012 :: Creating Dynamic Pivot Table

Jul 2, 2014

I am having trouble figuring out why the following code throws an error:

declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *

[Code] ...

The issue that I am having is:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.

I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).

View 6 Replies View Related

SQL Server 2012 :: How To Use Table Variable In SSIS

Sep 30, 2015

How to use table variable in SSIS 2012, is it possible to use table variable in SSIS.

I want to insert some results from EXECUTE SQL TASK to this table variable and use this variable in OLEDB SOURCE task in data flow where it is used in SQL query with IN Operation.

The table variable contain multiple values like '100','234','XYZ' Is it possible to do or is there any other solution to achieve this?

View 2 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2012 :: Table Variable Update Column

May 29, 2014

I am writing a query to update table variable. It is throwing me some error.

I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table

update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID

View 9 Replies View Related

SQL Server 2012 :: Table Variable In Stored Procedure?

Jul 29, 2015

What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?

Why can't the stored proc just have select statement instead of creating table variable?

View 3 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

SQL Server 2012 :: Creating A View Or Procedure From Dynamic Pivot Table

May 29, 2015

I have written a script to pivot a table into multiple columns.

The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.

Here is a copy of the script below

-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

[Code] ....

View 7 Replies View Related

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

Nov 4, 2015

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)

Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

View 2 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

Can We Set Result Of Dynamic Query To Variable?

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

How To Use A Table Variable With Dynamic SQL

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

Analysis :: Dynamic Declaration Of Variable In MDX Query?

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

Dynamic SQL Return Table Variable

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

SQL Server 2012 :: Getting Server Name Into A Variable Or Table

Sep 18, 2014

I am trying to store the server name from a query into a variable or table.

Declare @ServerName Varchar(30)=cast(@@SERVERNAME as varchar(30))
Print @servername

It keeps returning null.

View 9 Replies View Related

Is It Possible To Store The Result Of A Dynamic Query In A Local Variable

Dec 19, 2000

I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP

View 4 Replies View Related

Assing Dynamic Query Result To A Local Variable....

Jul 9, 2004

Hi:

Is there a way to assign a dynamic query result to a local variable?

declre @sqlString nvarchar(4000),
@minEventDate datetime,
@databaseName varchar(25)
selct @databaseName = 'customer_12345'
(actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)

select @sqlString =
'select ' + @minEventDate + '= (select min(eventDate) from ' + @databaseName + '.dbo.tblABC)'
exec sql_executesql @sqlString
print '@minEventDate: ' + cast(@minEventDate as varchar(19))

Though the select min(eventDate) from customer_12345.dbo.tblABC
returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.

As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.

thanks
-D

View 4 Replies View Related

String / Variable Problem - Dynamic Table Name

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

Dynamic Query, Local Cursor Variable And Global Cursors

Oct 3, 2006

Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View 3 Replies View Related

Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Aug 30, 2007

Hello all:

Here is a sample query:



DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'

DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'



DECLARE @KeyCol NVARCHAR(50);

SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR (1000)


SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +

' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '



The value resulting from Executing @KeyValue is an integer.

I want to store this value in a new variable say @VAR2

When I do this

DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue

its giving me an error.


can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?

View 3 Replies View Related

Dynamic OLE DB Table Source From Variable Not Seeing Input Columns

Dec 12, 2007

I am building an SSIS package that loops through a table in SQL Server and dynamically builds a select statement that i would like to use as an ole db source. I have been having a difficult time with this as the select statement that i am generating is over 200,000 characters long so using an sql variable is out of the question.

I ended up placing the select statement into a table where each row of the table represents a piece of the select. I then use an execute_sql task that selects the entire rowset from this table into a variable object. I then use a for each loop to shred the variable and concatenate it into on big string variable called user:: sql_statement that is my select.

After setting up the loop and testing to see if the user:: sql_statement variable populates correctly i then added a data flow transfer with an ole db source and destination. I then go into the advanced editor for the source and set it to accept an sql statement from a variable and use my user:: sql_statement variable. I was forced to set validate external metadata option to false to avoid an error since there is no way to validate the columns until the for each loop runs during run time.

Now thats all fine and good but what is causing my problem is that during run time, when the package gets to the data flow task, the select statement doesn't seem to be populating the input columns of the data source. I have been searching to no avail on a way to tell the data source to update the input columns but every time it gets there, the package bombs out telling me the ole db source has no available output columns.

Specifically the error i get is :
[DTS.Pipeline] Error: "output "OLE DB Source Output" (6616)" contains no output columns. An asynchronous output must contain output columns.

Any help with this would be much appreciated.

View 18 Replies View Related

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved