Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies


ADVERTISEMENT

Nested Queries, Stored Procedures, Temporary Table

Jul 23, 2005

Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.

View 5 Replies View Related

Variables For Table Names In Stored Proc

Nov 8, 2004

i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:

Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0

when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?

View 6 Replies View Related

Variables As Table Or Column Names In A Stored Procedure

Apr 16, 2008

I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks

View 2 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

Table Variables Vs. Temporary Tables - Need To Understand Something

Nov 5, 2007

Hi all,

I had a problem in a stored proc when I was using table variables in SQL Server 2005. I fixed the problem by changing them to temporary tables. It works now but I really want to understand why I had to do what I did. Here's the situation:

Had a stored proc that cached quite a bit of data (100-200k records with 7 columns) into a table variable. Then, it looped through this and returned a result set. The table this procedure was querying, then caching, was highly transactional. After about 2 or 3 users were hitting the same area at once, the procedure locked. If there were no locks, it executed very fast. However, it usually timed out (due to this locking).

After researching, I see that your usually better off sticking with temp tables for large amounts of data - but the primary explanation I hear for this is that you can index it. However, it also seems that the temp table will perform read-locking as its an actual physical table, whereas the table variable will not. Therefore, because there's no locking and its entirely in memory, the table variable is often the better choice. This is confusing me though because the problem I was experiencing was some sort of locking - which I thought would have been less likely with table variables.

The only other thought I had is that the table variables (if there were several of them existing at once due to several users executing the same procedure), were causing some kind of memory limit to be hit, making the database wait until some more memory became available.

Sorry for the long post, but I'd really like to know if anyone else has had these issues and what the cause might be.

Thanks,
Mike

View 4 Replies View Related

Search For A Table/string Within Job Steps, Dts, Database, Stored Procedures Etc

May 16, 2007

Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp

View 1 Replies View Related

Table Names And Variables

Nov 14, 2005

I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?

BTW both variables are of type char(100)

View 1 Replies View Related

Using Variables For Table Names In Query

Mar 19, 2006

Is it possible to use variable name to dynamically define a query in a stored procedure?  EX:

@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category

SELECT @Column
FROM @Table

View 1 Replies View Related

Use Of Variables In Table Names And Fields

Jun 22, 2006

Hi, I am new to using SQL for anything more in depth than querying and reporting.

I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?

quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);

insert into @a_tmptbl values ( "foobar", 21.76, 1);

select * from @a_tmptbl;

The error I am getting is:

quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.


Any advice would be gratefully accepted

View 3 Replies View Related

How To Use User Defined Variables As Table Names In SSIS

Dec 4, 2007

I am a SSIS novice, and need help to do the following:

Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.

I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error

TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------

In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?

One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query

exec( 'select * from ' + @table)

or just

select * from @table

Any suggestions will be appreciated.






View 10 Replies View Related

Looping Through Temporary Table To Concatenate A String

Mar 27, 2007

I have a large table that looks like this.
(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))
1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
can anyone help?
i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table)
thanks in advance

View 1 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

SQL Server 2012 :: Finding Procedures That Use Declared Table Variables?

Oct 22, 2014

know a way to find all stored procedures that use declared or temp tables, i.e

Declare @temptable TABLE as....

Create table #temptable

View 8 Replies View Related

(re)using A Temporary Table In A Stored Proc (was Confusion)

Feb 15, 2005

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?


Howdy!

View 14 Replies View Related

Temporary Table In 3 Diffirent Stored Proc

Mar 30, 2004

I know there maybe something similar of what im asking for but i just cant find it.

I have 3 Stored procedure.

SPA - create a temporary table "sp_getListOfChildren"
SPB - insert the data into the temp table "sp_InsertCategoriesFound"
SPC - display the list of categories i found "sp_ListingAvailableCategories"

process:
SPA call SPB and SPC call SPA

my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..

Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"

============MY "SPC" CODE=============
alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier
AS

exec sp_getListOfChildren @CurrentCategoryID

select * from #TblTempCat

select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID

View 7 Replies View Related

Insert Stored Procedure Result Into Temporary Table ?

Mar 21, 2006

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:


-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProc

View 5 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

May 29, 2015

I have a SQL text column from SP_who2 in table #SqlStatement:

like 1row shown  below :

 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"

I want to find what table and column  names are in the text ..

I tried like below ..  

Select B.Statement from #sp_who2 A  
LEFT JOIN #SqlStatement B ON A.spid = B.spid 
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C 
ON T.object_id=C.object_id
WHERE T.type='U'


Something like this : find the column names and tables name

View 18 Replies View Related

Replace String In A Table - Update Column Using Variables

Jul 3, 2014

Trying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw

USE Config

DECLARE @OldPath varchar(30), @NewPath varchar(30)

-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';

UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO

View 3 Replies View Related

Temporary Table In Stored Procedure Doesn't Work From SQLDataSource

Feb 20, 2008

I have a stored procedure with the following:


CREATE TABLE #t1 (... ...);


WITH temp AS (....)

INSERT INTO #t1

SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...


This stored procedure works fine in Management Studio.

I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.

What does the error message mean and what have I done wrong?

Thanks.

View 5 Replies View Related

SqlDataSource And Stored Procedures With Temporary Tables

Dec 20, 2007

Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table?  I am using Sql Server 2000.
The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it.
The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport
@AuditTypeName varchar(50), @PlantId int = NULL,
@BuildingId int = NULL, @AreaId int = NULL,
@WorkCellId int = NULL, @WorkShift int = NULL,
@StartDate datetime = NULL, @EndDate datetime = NULL,
@debug bit = 0 AS
 CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money)
DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000)
 SELECT @sql = '
INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
(ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent)
SELECT DISTINCT QualityControl_Audit_Item.Header,
QualityControl_Audit_Item.AuditItem,
QualityControl_Audit_Item.HeaderSequence,
QualityControl_Audit_Item.AuditItemSequence,
NULL, NULL
FROM QualityControl_Audit INNER JOIN
QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id
WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)'
IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId'
IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId'
IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId'
IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId'
IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift'
IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate'
IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + '
ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence'
IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int,
@xWorkShift int, @xStartDate datetime, @xEndDate datetime'
EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR
SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
OPEN my_cursor
--Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
--Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Major
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
--Minor
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable

View 8 Replies View Related

SQL SERVER TEMPORARY TABLES In STORED PROCEDURES

Jun 3, 2006

There are two ways to create a temporary tables in stored procedures

1: Using Create Table <Table Name> & then Drop table

ex. Create Table emp (empno int, empname varchar(20))

at last : drop table emp

2. Using Create table #tempemp

( empno int, empname varchar(20))

at last : delete #tempemp

---which one is preferrable & why.

what are the advantages & disadvantages of the above two types.



View 5 Replies View Related

Difference In Creating Temporary Table By #table And ##table

Nov 29, 2006

Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply
Banti"

View 1 Replies View Related

Dynamic Table Names In Stored Procedure...

Jul 2, 2007

Hello all,

Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name???

Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

Cheers,
Justin

View 4 Replies View Related

Stored Procedure Table Names To View

Sep 7, 2005

I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.

View 3 Replies View Related

How To Pass Table Names To A Stored Procedure

Dec 16, 2005

Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi

View 5 Replies View Related

Table Variables In Stored Procedure

Jan 11, 2006

I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS.  I know there was a problem doing this in DTS, which would result in an Invalid Pointer error.  I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider."  The stored procedure runs fine on it's own.

Any thoughts?

 

 

View 24 Replies View Related

Passing Variable Table Names To Stored Procedure

May 5, 2000

I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.

Select * from @Passedtablename. This won't work. Any insights.

View 1 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!

View 11 Replies View Related

Is It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?

Mar 10, 2008

Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789

View 2 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Temporary Table Vs. Table Valued Function

Jun 6, 2006

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.

View 4 Replies View Related

Stored Procedures Variables

Dec 28, 2005

I have a PROC that I want to modify via a variable I will pass in from ASP.Net.

Currently the PROC is as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(30)
AS
Set @Search = '%'+ @Search + '%'

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (dbo.EDCclient.CLIENT like @Search) OR
(dbo.EDCproperty.PROPERTY like @Search)

I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(200)
AS

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID

@Search)

But I can't get SQL to accept the @Search in place of the Where... Any ideas?

View 7 Replies View Related







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