Sp_executesql - Using Table Variable

Jan 18, 2008

In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
 DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU
 DECLARE @Count int
 DECLARE @ItemNumSKU varchar(50)
 SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
 EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
 IF (@Count = 0)
  BEGIN
   SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
   EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
   SET @ItemNumber1 = @ItemNumSKU
  END

-- Creating table variable to have values from 1 to 12
 DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
 DECLARE @Cnt INT
 SET @Cnt = 1
 WHILE(@Cnt <= 12)
 BEGIN
  INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
  SET @Cnt = @Cnt + 1
 END

--Joining query result with the table variable to get required result 
 DECLARE @Status1 Char(1)
 SET @Status1 = 'C'

 SET @SQL = N'SELECT'
 SET @SQL = @SQL + N' MS.MonthNumber,'
 SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
 SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
 SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
 SET @SQL = @SQL + N' FROM'
 SET @SQL = @SQL + N' (SELECT '
 SET @SQL = @SQL + N'  DATEPART(mm, DateTime) Month#'
 SET @SQL = @SQL + N'  ,SUM(CostPer*Quantity) As MonthCost'
 SET @SQL = @SQL + N'  ,SUM(PricePer*Quantity) AS MonthPrice'
 SET @SQL = @SQL + N'  ,SUM(Quantity) AS MonthQuan '
 SET @SQL = @SQL + N' FROM '
 SET @SQL = @SQL + N'  Invoice_Totals '
 SET @SQL = @SQL + N'  INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
 SET @SQL = @SQL + N' WHERE '
 SET @SQL = @SQL + N'  Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
 SET @SQL = @SQL + N'  AND ItemNum = @ItemNumber'
 SET @SQL = @SQL + N'  AND DATEPART(yy,datetime)=@Year'
 SET @SQL = @SQL + N' GROUP BY'
 SET @SQL = @SQL + N'  DATEPART(mm, DateTime)'
 SET @SQL = @SQL + N' ) Temp '
 SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
 SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

 EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1

 

Thanks

View 6 Replies


ADVERTISEMENT

Sp_executesql - Using Table Variable

Jan 18, 2008

In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END

-- Creating table variable to have values from 1 to 12
DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END

--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'

SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1



Thanks

View 1 Replies View Related

Concetenate Variable In Sp_executesql

Sep 5, 2007



Hi

I am trying to cinstruct two strings/variables and conetenate it in sp_executsql

Isnt this the right way to do it ?


CREATE PROCEDURE dbo.XML_Final

(
@Filepath varchar(50),

@DBName varchar(20))

AS

DECLARE @hdoc int

DECLARE @doc varchar(max)

--DECLARE @doca varchar(max)

--DECLARE @SQL nvarchar(200)

DECLARE @INSERT nvarchar(max)

DECLARE @WITH nvarchar(max)

SET @SQL = N'select @doc = c from OpenRowset(BULK''' + @Filepath + N''', SINGLE_CLOB) as T(c)'

exec sp_executesql @SQL, N'@doc varchar(max) output' ,@doc OUTPUT

EXECsp_xml_preparedocument @hdoc OUTPUT, @doc

SELECT @INSERT = N' INSERT INTO'+ @DBName + N'.dbo.Student( Name,Age,Address)'

--SET @WITH = N @TEMP1

SELECT @WITH = N' SELECT * FROM OPENXML (@hdoc,'+N'''/Name/Age'/Address+N''')' +N'
WITH

(

Name text'+ N' ' + '''../../Name' + N ''',

Age int' + ' ' + N'''../../Age+ N''',

Address int'+ ' ' + N'''../../Address'+ N'''

)'

exec sp_executesql @INSERT+@WITH

-- Remove the internal representation.

EXEC sp_xml_removedocument @hdoc


Gives me a error at

exec sp_executesql @INSERT+@WITH :

Incorrect syntax near '+'

Thanks

View 1 Replies View Related

Problem Running SP_EXECUTESQL With OpenRowSet (with Variable) As Part Of The Query

Nov 27, 2007

Hi,

I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?


DECLARE @SqlToRun NVARCHAR(MAX)
DECLARE @RESOURCEIDS NVARCHAR(MAX)

SET @RESOURCEIDS = REPLICATE(CAST('ABC' AS NVARCHAR(MAX)), 1000)

SET @SqlToRun = N'SELECT * FROM
OPENROWSET(''SQLNCLI'',
''server=;database=;uid=;pwd='',
''SELECT * FROM WHERE COL=@RESID'')'

DECLARE @PARAMS NVARCHAR(MAX)
SET @PARAMS = ''@RESID NVARCHAR(MAX)'

EXECUTE SP_EXECUTESQL @SqlToRun, @PARAMS, @RESID = @RESOURCEIDS

Regards,
Derek

View 5 Replies View Related

Using Sp_ExecuteSQL And Passing Table Name

Jan 11, 2001

I am creating a dynamic sql statement in a stored procedure.
The stored procedure has table name passed in.
I cannot pass in the table name as a parameter.

How do I pass the table name as a paramter is a dynamic sql statement

View 1 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

How To Put The Sp_executesql Result In A Temp Table

Apr 13, 2001

I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?

View 1 Replies View Related

How To Pass Table Parameter With Sp_executesql

Sep 4, 2007

Hello All,
i am using

execute sp_executesql @strQuery statement in my SP.
and My is @strQuery = "Insert into @tmp_tbl select ...". I have created @tmp_tbl using "

DECLARE @tmp_tbl TABLE(rownum ...... ".

its giving me error "

Must declare the table variable "@tmp_tbl". while executing : execute sp_executesql @strQuery statement.

How to pass that table variable with executesql ??
As i understand, i created table @tmp_tbl in my SP and trying to access it inside system SP sp_executesql .
so its not working.

Is there a way to pass table variable as parameter to sp_executesql ??

Thanks in advance. its very urgent for me..

View 8 Replies View Related

Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery

Aug 21, 2007

Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.

now instead of select query , i want to creat hash table.
so i wrote :

set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through

execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.

Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .


please guide me how to do this?
its very urgent for me.
thanks in advance.

View 4 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?

Sep 17, 2007

In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

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

Indexes On Table Variable Of Table Valued Function

Jan 6, 2004

Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View 2 Replies View Related

How To Create Index On Table Variable (Table Don't Have Primary Key)

Feb 26, 2008



Hi all,


my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...


DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)

I tryed all below senarios...it is giving error...


--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))


View 3 Replies View Related

The Age Old Argument Of Temp Table Vs Table Variable

Jan 26, 2007

Hi All,Hope someone can help me...Im trying to highlight the advantages of using table variables asapposed to temp tables within single scope.My manager seems to believe that table variables are not advantageousbecause they reside in memory.He also seems to believe that temp tables do not use memory...Does anyone know how SQL server could read data from a temp tablewithout passing the data contained therein through memory???Is this a valid advantage/disadvantage of table variables VS temptables?

View 2 Replies View Related

Delete SQL Table Using A Variable That Refers To The Table Name

Jul 20, 2005

SQLLY challenged be gentle --Trying to create code that will drop a table using a variable as theTable Name.DECLARE @testname as char(50)SELECT @testname = 'CO_Line_of_Business_' +SUBSTRING(CAST(CD_LAST_EOM_DATEAS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),4)+ '_' + 'EOM'FROM TableNamePrint @testname = 'blah...blah...blah' (which is the actual tablename on the server)How can I use this variable (@testname) to drop the table? Undersevere time constraints so any help would be greatly appreciated.

View 2 Replies View Related

Table Variable Versus #Temp Table

Jan 17, 2008

In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1

It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.

Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?

Thanks in advance for any help.

View 4 Replies View Related

Table-valued Function Run Once For Each Row In A Table Variable.

Mar 19, 2008

I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.




Code Snippet

declare @IdTable table
(
EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees

/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )







Any help is very much appreciated.
Andrew

View 3 Replies View Related

Table-valued Function Into A @table Variable

Feb 22, 2008

In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?




ALTER PROCEDURE [dbo].[rpt]

(


@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'

)
AS


--split CourtIDs into a table
DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View 6 Replies View Related

Should I Use Temporary Table Or Table Variable?

Apr 1, 2007

hi all, if i have a function which it returns a table and i need to work with the table retured many times in the stored procedure, then should i use a temporary table or a table variable to store the returned table ? or it's there a better way in doing that? 

View 1 Replies View Related

Table Variable Vs Temp Table

Oct 15, 2004

I have read alot of articles saying that we should use Table variable for small volume of data, Temp table for large volume of table.

However, how to determine small and large??? Willing for your comment and answer..

View 1 Replies View Related

Temporal Table Vs. Table Variable

Nov 26, 2004

Hello, everyone:

I want to save some temporal data in the stored procedure. Comparing temporal table and table type variable, which one is better regarding to the performance?

Thanks

ZYT

View 1 Replies View Related

Update Table Variable From Another Table

Sep 10, 2007

I need to update a table variable with a sum from another table. Something like this:


DECLARE @MyTableVariable Table

(

Location varchar(10),

ItemNumber Varchar(10)

Amount money,

Quantity int

)



UPDATE @MyTableVariable

SET Quantity =( SELECT SUM(Quantity)

FROM myTable

WHERE @MyTableVariable.[Location] = myTable.[Location]

and

@MyTableVariable.[ItemNumber] = myTable.[ItemNumber] )

The where clause does not work though. Is there a way to do this?

View 5 Replies View Related

Temporary Table And Table Variable

Apr 14, 2008

what is the difference between Temporary table and Table variable ,need one example with both(#t,@t) to know the scope

View 10 Replies View Related

Table Variable V/s Temp Table

Sep 20, 2007



Hello,
Which is better? Using a table variable or a temporary table?

Mannu.

View 3 Replies View Related

When To Use Temp Table And Table Variable

Nov 17, 2015

in order to store huge data, should i use temp table or table variable(performance wise) and why?

View 2 Replies View Related

Row Of Table, To A Variable

Oct 17, 2005

I'm a new member of forum,
I'm a Brazilian, and don't speak english very good...
But, i need help...

    private void Page_Load(object sender, System.EventArgs e)
        {
           
string ConnectionString = "server=Server;database=db;user
id=XXX;pwd=YYY";
            string CommandText = "SELECT * FROM usuario";

           
SqlConnection myConnection = new SqlConnection(ConnectionString);
           
SqlCommand myCommand = new SqlCommand(CommandText, myConnection);

            myConnection.Open();

           
dgrid.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            dgrid.DataBind();
        }

I'm want pass a value of database table to a variable, how to make it ?

View 1 Replies View Related

Variable As Table Name In T-SQL

Jun 5, 2001

I cannot seem to pass a table name to an sp as a variable without having to construct the sql string first.

i.e i want to do this...

CREATE Procedure dbo.proc_sectranssummary
@tblname nvarchar (30)
AS
/************************************************** ******************/
CREATE TABLE @tblname (
Heading nvarchar (100),
Count_BatchID integer,
Sum_Units float,
Sum_AccruedIntDays integer
)
etc.

BUT it wont work and can only do it this way...


CREATE Procedure dbo.proc_sectranssummary
@tblname nvarchar (30)
AS
/************************************************** ********************/
DECLARE @sql nvarchar (2000)

SELECT @sql =

'CREATE TABLE ' + @tblname + '
(
Heading nvarchar (100),
Count_BatchID integer,
Sum_Units float,
Sum_AccruedIntDays integer
)'
EXEC(@sql)

etc.

but this makes it difficult when going on to use the table later on in INSERTS etc in the same sp.

pls Help!!

Palmy

View 1 Replies View Related

Using A Variable As A Table Name

Jan 2, 2003

Help!
I am writing a cursor that goes out and retrieves all the tables that contain a certain column name and then inserts specific data from those tables into a new table. The problem i'm having is even though I can load the table names into a variable I can't use that variable as a table name to do the insert. It gives me an error that the variable hasn't been declared even though it has. Is there any tricks I can do to make SQL treat the variable as a table name so I can run the Insert from these table names?

Dave

Here is the what I've got so far:

Declare @table_name varchar
Declare table_name_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES as t, syscolumns as s
WHERE t.TABLE_NAME = OBJECT_NAME((s.ID)) and s.Name='ModBy'and t.Table_Type = 'Base Table'

CREATE TABLE
##oldusers
(
NameIDint,
OldestDt Datetime
)
;

Open table_name_cursor

Fetch Next From table_name_cursor
Into @table_name

WHILE @@FETCH_STATUS = 0
Begin

Insert into ##oldusers(NameID,OldestDt)
Select Addby, Max(AddDt)
FROM @table_name<---This is the problem.
group by Addby

Fetch Next From table_name_cursor
Into @table_name
End
Close table_name_cursor
Deallocate table_name_cursor
GO

View 2 Replies View Related

Table Name As A Variable? UDF?????

Jun 10, 2002

HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!

I have a UDF that will return a fully qualified table name given a few parameters. What I would like to do is call my UDF in a SQL statement and NOT have to use the sp_executesql and pass my query in as a string.

The reason for this is I have no promises from my client of the server and database name for the tables that I am going to use so I would like to build a UDF that I could change the server name and database name in ONE place and have all my queries using the right database. I basically have 3 "applications' that I need to do business with. For simplicity sake we'll just call them 'APP1','APP2','APP3'.

Example:

If my table is called 'TABLEA' and it's a table for 'APP1', what I have is UDF where you pass in the application and table and it returns the fully qualified path to the table. For example:

Select @TheTableName = dbo.MYUDF('app1','tablea')

Result is @TheTableName would be 'SERVER_FOR_APP1.DBO.TABLEA'

What I have found that I had to do is build a nvarchar(????) string of my query and then call sp_executesql and pass it the query via the nvarchar string. There are cases where this will not work - For example:

Select @somevalue = sometable.field from server_for_app1.dbo.sometable where someprimarykey = 123

Ideally I could code this as:

Select @somevalue = sometable.field from MyUDF('APP1','SOMETABLE') where someprimarykey = 123

Again, "MyUDF" returns a varchar() of the fully qualified table name including server and database name.

Does anyone know how I can make this work?

Thanks for any help you can provide and apologies for any cross posting!

Sincerely,

Dave Dulong

View 1 Replies View Related

Table Variable

Jun 8, 2004

May I have an idea about table variable?I want to know the scope and capacity of a table variable?How much data can it hold maximum?

View 1 Replies View Related







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