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

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

need to store the value of Execute SQL(dynamic sql) into variable.

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

Hi guys, I need to store the value of my dynamic sql query into a variable

Basically i am passing a table name and then doing a count(*) to get the number of rows but having difficulty in storing the value of the Exec(@sql) into a variable.

Pls help


Select @sql = 'Select' + CAST(@TRCnt AS Varchar) + ' = count(*) From [' + @TTableName  + ']'



View Complete Post

More Related Resource Links

How to store and retrieve multiple values in a single session variable .


I want to store employee name,designation and department in session variable and
retrieve in another page how to do this.

IF EXISTS(then how to store fld value in variable)


Dynamic SQL query cannot store more than 4000 characters even with NVARCHAR(MAX)

Hello, everyone. I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql. One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable! I use SQL Server 2008. All the documentation online I checked states that the solution is just using NVARCHAR(MAX) and the problem should not happen. How can this keep happening? Cheers, Ivo Pereira IT Consultant PortugalComputerDoc

Unable to store for xml Xpath result in a variable

HI ALL, Unable to store the results of xml  reults into a varible , it  throws an error declare   @r varchar(1000) set   @r =( WITH   XMLNAMESPACES (     'http://schemas.microsoft.com/analysisservices/2003/engine/2/2' as ddl2_2   , 'http://schemas.microsoft.com/analysisservices/2003/engine/2' as ddl2   ,'http://www.w3.org/2001/XMLSchema-instance' as xsi   ,'http://www.w3.org/2001/XMLSchema' as xsd ) select   DatabaseID "Object/DatabaseID" , CubeID "Object/CubeID" , MeasureGroupID "Object/MeasureGroupID" , PartitionID "Object/PartitionID" , DimensionID "Object/DimensionID" , ProcessType "Type" , WriteBackTableCreation "WriteBackTableCreation" , Parallel "parallel"   from #ega WHERE   ProcessingGroupID = 3 FOR   XML PATH('Process'))

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

How To Create Dynamic Insert Store Proc


I want to Create a Insert Procedure for Table which has Three Coloum I want to use this Insert Store Proc for another table which has two coloum. If it works then I don't Have to create more Store Proc for Every Table One Insert Proc will Serve my Purpose I am My giving my proc below

Create Proc All_Insert


@Name varchar (30),

@Phone varchar (15)

@Mobile Varchar (15)





insert into Table1














can dynamic sql be used against a table variable?


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 + ''')')


how to store the values in a object variable (package level scope) in a script component

I have declared a variable of object type with package level scope.  I want to assign values to this variable inside a script component.  My object should hold 5 columns in a row.  How to implement this ?

Variable to Store temporary page data


I'm working on a web page that is going to be used to enter data into a database. The page is going to be dynamic in that the user has the ability to add a row to the table with the same fields, ie lname1, fname1, mi1 then lname2, fname2, mi2

I'm wanting to use a variable to keep track of how many lines there are so that when I generate the added line I am creating a new field with the ID of lname+# . Whats the best way to do this, a global variable, session variable? As side note, first time programming in .Net or C# so I apologize for my lack of knowledge.

how to store query result in variable


hello experts..

i am wishing to store the query result in any variable, please have look at below code.


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim conn As New SqlConnection(GetConnectionString())
Dim str As String = System.Web.HttpContext.Current.Request.Url.AbsolutePath
Dim previous = "SELECT views FROM Articles WHERE ([url]=@str)"
previous = previous + 1
Dim sql As String = "INSERT INTO Articles (views) VALUES (@previous)"
Dim cmd As New SqlCommand(sql, conn)
Dim pram As SqlParameter() = New SqlParameter(1) {}
pram(0) = New SqlParameter("@previous", SqlDbType.VarChar, 50)

pram(0).Value = previous

For i As Integer = 0 To pram.Length - 1

cmd.CommandType = CommandType.Text

Execute DOS Command with Variable



I am using 2 task in control flow.

1st task is a "execute SQL task" which returning from DB into a variable called VAR1(i.e. VAR1 = C:\test_data\)

2nd task is "execute process task" where I wnat to delete all the files from the directory (i.e. value got in VAR1)

Pls help.

Many Thanks,





Execute SQL Tast output into a Variable in SSIS




I´m trying to put the count or the max value of an slq statement in to a variable and I´m not able until now.


I have a global variable named vMax and a OLE DB connection to a DB.

The Quiery works fine and a get a result depending of Count or Max from 15000 for Count and 2000849400 for Max.


I tried all Variable types as int16, int32, double,... nothing works and I get allways the same error mesage.


Error: 0xC001F009 at xyz: The type of the value being assigned to variable "User::vMax" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.


I tried now everything and I was not able I have now only this execute SQL Task in my project and it does not work.


My goal is to copy all new records from Table1 in to Table2.

So I create an Execute SQL Tast to get the max ID and to use this for a sql command in the OLE DB Source.


Thank you in advance for any help.




Addition:  _____________________________________________________________________________

Im still trying to get this work and found one thing out. The ID that I'm using to find the Ma

store textbox(inside the gridview) information in single variable".


Hi Friends.
Im using gridview. It contain Name, Sex, Mobile and Message
That Message details have to display in textbox(Inside the GridView),
Now I want store textbox information in single variable".

using dynamic connections in Execute SQL Task


I want to use an Execute SQL Task to read data from a table in a database on server1 and copy that data to server2. The servers are not linked. I set the connection information for server1 in the task properties but how do I dynamically incorporate server2 into the insert as select statement? I have tried the following where @TO_SERVER is a passed in variable containing the name of server2. But it didn't work:

SET @nsql = 'INSERT INTO [' + @TO_SERVER + '].myDB..myRegionTable 
SELECT region_id,REGION_desc,create_date
FROM differentDB.dbo.myRegionTable a
WHERE a.region_id+a.region_desc+CONVERT(varchar,a.create_date,9)
 (SELECT b.region_id+b.region_desc+ CONVERT(varchar,b.create_date,9)
 FROM [' + @TO_SERVER + '].myDB..myRegionTable b
 WHERE a.region_id+a.region_desc+CONVERT(varchar,a.create_date,9)=
  b.region_id+b.region_desc+ CONVERT(varchar,b.create_date,9)

exec sp_executesql @nsql

Both the source and destination are SQL Server 2008 R2 databases. I don't want to use the dataflow task because I only want to insert into the tables where the values aren't already there.

I have a connection in Connection Managers for both server1 and server2 but do not know

Assign a local variable a value via dynamic sql


declare @myint int,

@query = varchar(4000)

set @query = "select max(id) from[" + dbo.fnGetDBName()  + "].dbo.mytable"

set @myint = exec @query  -- fails

DBCC Checkident reseed(mytable,reseed,@myint)

--more processing statements--

trouble is I cannot get @myint to be asigned.

I can take the long route and create a temp table and assign the value after selecting the first row. I tried sp_executesql which works, but nothing else is processed after that.


Adding multiple attachment (files) to a dynamic variable so it can be send to a email. VB 2010


Hello !

I need some advice on what to use for adding multiple attachment to a email. I'm using VB 2010.

First, I have a Form in my company web site to fill a reclamation ..... when you fill and send it, it convert the form into a PDF, save it in the client side "TEMP" and finally get the PDF and send it to a email..... that's perfectly  fine. I add to the form the capability of attach multiple files with file upload to the same "TEMP" and displayed in a Grid View to remove or add files and send everything to a email, the attachment capability is working. Everything is working with the PDF not the files(attachments). 

There is a way to get the multiple files from the "TEMP" folder and add it to some dynamically variable (array list, attachment collection, list collection, etc.) so all can be send to the email. I already know the way to put it into the email with the PDF. The problem is to get the files and add it to the dynamic variable and that it work with the email.

If the code is need it, let me know.


Copying rows using a dynamic SQL Command variable


I have a table with the Source TableName and Source ColumnName. I am trying to use SSIS to extract data from my source based on the tables and columns. I am using a Script Task to dynamically create the SELECT statement, no problems with that.

Next, I have a data flow task with a Source OLE DB Connection and the Data Access mode is using this dynamically created select statement. I am however unable to use this because this SELECT statement variable is not populated until runtime.

Basically I want to COPY ALL ROWS from the source to a destination. 

Is this possible? What is wrong with my assumptions? Any suggestions?


You are so wise...like a miniature budha covered in fur. -Anchorman
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