.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

can dynamic sql be used against a table variable?

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

This Works

create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)

This does not work

Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)

Error Msg:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.

Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?

The goal is to be able to pass in a string param like this to a proc

s1 = 'aa','bb','cc'

declare @s1 varchar(20)

Begin As

declare @tmp1(...)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In ('' + @s1 + '')')


View Complete Post

More Related Resource Links

Dynamic Column filter on table variable or temp table



I created a  procedure which first populates a table variable /temp table with data. Then based on input parameter (say, @ColumnNames) I want to filter the result set.

Here is the snippet I have created, -

create PROCEDURE [dbo].[GetAlerts]
	@roleId varchar(20),
	@fields sysname
declare @temp table(
	ID int,
	role_id varchar(20),
	CRITICALINFO varchar(1000));

    insert into @temp select * from dbo.commonalertdata where role_id= @roleId);

	declare @sql varchar(300);
	set @sql='select ' + @fields + ' from @temp';
	exec dbo.sp_executesql @sql; 

Then i tried to run it as follows, -


And It gave the following error.

Msg 1087, Level 15, State 2, Line 1

Must declare the table variable "@temp".

Now, I tried using table variable or temp table or even UDF with no good. Can anybody help me in getting dynamic columns from a calculated table valued result set like temp table / table variable / inline table valued UDF?

create and insert into a dynamic sql table at runtime

Below I have a bunch of SQL statements that creates a table at runtime and the "pic" column is a dynamic column and the code creates the number of "pic" columns depending on the count of the uploader control.  the problem comes when I try to insert into that table and I am trying to say for every "pic" declare variable @pic.... it works well for just one file in the uploader but for multiple files i get the following error The name "pic1" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Directory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")        Dim objConn As New SqlConnection("Data Source=xxxxxxxxx.hostedresource.com; Initial Catalog=mrpoteat; User ID=xxxxxxx; Password=xxxxxxxxx;")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandTex

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

Custom component Table name dynamic table name

Hi All How to i call the table dynamiclly in to C# class library. I can able to call the columns names.. but i unable to call the table name.. please suggest it Example:   IDTSOutputColumn100 column = output.OutputColumnCollection[x]; columnInfos[x] = new ColumnInfo(); columnInfos[x].columnName = column.Name; columnInfos[x].Column_reference = column.CustomPropertyCollection[ "Column_reference"].Value.ToString(); columnInfos[x].bufferColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID); columnInfos[x].lineageID = column.LineageID;   geting the columns names .. how to i get table name...?? Regards Ram

insert into dynamic SQL Table at runtim

the follow ing is supposed to create a table and insert the columns into the table at runtime.  It also has picture column and creates the amaount of picture columns based on the amount of files in the multiple file uploaer.. it works fine with just one file in the uploader.  The problem comes when there  are two or more files in the uploader and intead of adding the second image right after the first image is added, it instead adds the first image then goes back and trys to create the table again, and i get an error saying the table already exsits.I want it to add the second image right after the first image, not go back and create the table then add the second imageDirectory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")              Dim objConn As New SqlConnection("Data Source=xxx")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandText = "

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Using EXEC with a table variable?

I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )   exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2   ---------------------------------------------------------------------------------------------------------------------------------- I tried insert @a execute dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2    but got this error: Msg 197, Level 15, State 1, Line 6 EXECUTE cannot be used as a source when inserting into a table variable.   How can i do this.   Thanks.

Stored Procedure utilizing table variable

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

Need help with dynamic table generation

I am building a test script to help myself figure out dynamic SQL.  My goal is to generate column names from an existing table query.  To work in this direction I made the following test: declare @sql varchar(1000) declare @int int set @int = 1 set @sql = 'declare @test table (' set @sql = @sql + 'testfield varchar(10),' while @int < 5 begin  if @int < 4   Begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10),'   end  else if @int = 4   begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10))'   end  set @int = @int + 1  end select (@sql) exec (@sql) insert into @test (testfield, field1, field2, field3, field4) values (1,2,3,4,5) When I run this query I get the following from the select (@sql) query: declare @test table (testfield varchar(10), field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10)) This looks good.  When I run the exec statement, this returns 1 Row affected, so, that seems to work.  However, when I run the insert I get this error: Server: Msg 137, Level 15, State 2, Line 23 Must declare the variable '@test'. I know that GO statements will effectively create batch dividers between statement groups, but, I haven't yet learned

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Get value of dynamic textbox in dynamic table with masterpages

Public Sub createtable() Dim b as Integer = 1 For i As Integer = 1 To b Dim tbl As New Table() Dim tr0 As New TableRow() Dim tr1 As New TableRow() Dim tr2 As New TableRow() Dim trh As New TableRow() Dim th As New TableCell() Dim td As New TableCell() Dim td0 As New TableCell() Dim td1 As New TableCell() Dim td2 As New TableCell() Dim td3 As New TableCell() Dim td4 As New TableCell() Dim td5 As New TableCell() Dim td6 As New TableCell() Dim td7 As New TableCell() Dim td8 As New TableCell() Dim td9 As New TableCell() Dim td10 As New TableCell() Dim td11 As New TableCell() Dim title As New DropDownList() Dim fname As New TextBox() Dim mname As New TextBox() Dim lname As New TextBox() Dim address As New TextBox() title.ID = "txtTitle" title.Items.Add("Mr.") title.Items.Add("Ms.") title.Items.Add("Mrs.") fname.ID = "txtfname" mname.ID = "txtmname" lname.ID = "txtlname" address.ID = "txtaddress" th.

dynamic table name linq-to-sql query


I am writing a function in which i generate autocode in specific format i want to make this function generalize so that i send tablename and columnname  parameter and function return the next code. I am using linq to sql with MVC e.g. i want to make table name and column name dyanamic

from m in db.<tblnameparam> 
                       orderby m.<colnameparam> descending
                       select m.<colnameparam>

Performance Drop when switching from a #temp table to a @temp table variable


I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?


Add very dynamic controls to a asp:table


 Hi all. I want to create a TableRow with a TextBox. I want when click a Button a TableRow add to asp:table. in Button_Click event code I can add a row by a TableRow & TableCell & TextBox Objects and with help a HiddenField.
My problem is that: after add first row that has a textbox, next row doesn't add by click button. I think Controls delete after post back in server and first textbox delete and new textbox add same position. what is solution after every click, a row with textbox add to the table?

            TableRow tr = new TableRow();
            TableCell tc1 = new TableCell();
            TableCell tc2 = new TableCell();
            TableCell tc3 = new TableCell();
            TextBox txt1 = new TextBox();
            TextBox txt2 = new TextBox();
            DropDownList drp = new DropDownList(); drp.Width = Unit.Pixel(100);


Variable value within SQL Server table Configuration is not being updated within load of DTSX packag



I am probably missing something obvious here; -
I have created a package with a variable named; - ServerName and a value of 'KIERAN-PC'
I exported this variable as an SSIS configuration within a SQL Server table

I noticed the following record appeared within the SQL Server configuration table.


ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend