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


ADVERTISEMENT

Passing Table Names As An Argument In A Procedure

May 7, 2008

hai <br>
<p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p>
CREATE PROCEDURE outputtable @tablename nchar(10) AS<br>
begin<br>
.........................<br>
insert into @tablename (classno) values (@cno)<br>
.................................<br>
END<br>
 
<p> I get the following error message :</p>
Msg 1087, Level 15, State 2, Procedure................<br>
Must declare the table variable "@tablename".<br>
 <p>
Is there anyway i can get around this one.   I will be grateful if anyone can help me</p>

View 5 Replies View Related

Problem Passing A Variable To A Stored Procedure

Dec 7, 2007

I've got stored procedure:
  ALTER PROCEDURE [dbo].[dropmyValue](@dropVal Char OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] DROP CONSTRAINT ' + @dropVal)  That gets it's value from a GridView.SelectedValue:
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)        If GridView1.SelectedValue.ToString <> "" Then            Dim cs As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString            Using con As New SqlConnection(cs)                con.Open()                Dim cmd As New SqlCommand                cmd.Connection = con                cmd.CommandType = CommandType.StoredProcedure                cmd.CommandText = "dropmyValue"                cmd.Parameters.Add("dropVal", SqlDbType.Char)                cmd.Parameters("dropVal").Direction = ParameterDirection.InputOutput                cmd.Parameters("dropVal").Value = "DF_" + GridView1.SelectedValue                Label2.Text = cmd.Parameters("dropVal").Value                cmd.ExecuteNonQuery()            End Using            GridView1.DataBind()        End If    End Sub 
Label2.text shows that @dropVal is "DF_xxxxxxxx" and is the name of the Constaint to be dropped (when I comment out "cmd.ExecuteNonQuery" and run it), but the error I get is that
" 'D' is not a Constraint ". I don't know if this is a sqldbtype problem, but I've tried different ones and evidently only the first character "D" is getting read, or passed to the stored procedure.
Any help would be appreciated.
Steve

View 3 Replies View Related

PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE

Sep 24, 2007

Hi
I am WORKING IN AN APPLICATION USING SQL SERVER 2000 AND VB6
I'VE A PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE
I WILL EXPLAIN WITH AN EXAMPLE

TABLE STRUCTURE
AccAccounts
------------------
Accid(Numeric) AccName(Varchar)
-------------------------------------------------
1 Cash A/c
2 Students A/c
3 HDFC Bank A/c

my Application will pass the "Accid" as a string format to Stored Procedure
STORED PROCEDURE
-----------------------------------
CREATE PROCEDURE GetAccName
@Accid Varchar(100)
AS
Select * from Accaccount where accid in (@Accid)


when i run this SP
declare @Accid Varchar(100)
set @Accid ='1,2'
exec GetAccName @Accid

i get the following error
Server: Msg 8114, Level 16, State 5, Procedure GetAccName, Line 4
Error converting data type varchar to numeric.

please "ANY ONE" help me!!.
The above example is only an example

REGARDS
JAMES



View 8 Replies View Related

Variable Names Must Be Unique Within A Query Batch Or Stored Procedure.

May 4, 2007

hi ,

i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...



The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.

thanx in advance

raj

View 5 Replies View Related

Help Passing A Stored Procedure Reults To A Local Variable?

Apr 29, 2005

I'm trying to do something like this in SQL Server:
<code>
CREATE PROCEDURE sp_insert_proc
(
@item1 as int,
@item2 as varchar(50),
)
DECLARE @LocalVariable AS varchar(50)
SET @LocalVariable = dbo.sp_storedprocedure
INSERT INTO Table
(
Column1,
Column2,
Column3,
)
VALUES
(
@Item1,
@Item2,
@LocalVariable
)
</code>
Is this possible?
How can I return the results of the stored procedure to a variable so I can use those results

View 1 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View 7 Replies View Related

Passing SSIS Package Variable To Stored Procedure As Parameter

Feb 25, 2008



I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax

in Execute SQL Task Editor I have
conn type -- ole db
connection -- some server
sql source type -- direct input
sql statement -- exec testStoredProc @timeStamp = ?

if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008'

This is the syntax I found to execute the procedure, I don't udnerstand few things about it.

1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters.

2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime

3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1.

Thank you, please help.

View 2 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

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

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

Passing Table Name To Stored Procedure Dynamicaly

Dec 27, 2007

 It's my code: 1 CREATE PROCEDURE SearchFunction2 @SQ nvarchar(30),3 @pType nvarchar(11),4 @pCol nvarchar(11)5 AS6 BEGIN7 SELECT * FROM [data] 8 WHERE ([type] LIKE '%'+@pType+'%') AND (@pCol LIKE '%'+@SQ+'%')9 END
10 GOWhen I replace '@pCol' with 'nameCol' it works fine, but when i pass it trough parameter in my aspx page [pCol.Value='nameCol'] it does not work!  

View 2 Replies View Related

Passing A Dynamic Table Name To A Stored Procedure

May 19, 2004

Is this possible?

I know this doesn't work but I'll post it anyways so you can see what I'm trying to accomplish.



CREATE PROCEDURE GetStuff
@something int,
@tablename varchar(50)
AS
SELECT * FROM @tablename WHERE something = @something



Like I say, I know it doesn't work...but does anyone know how to accomplish something like this?

View 1 Replies View Related

Passing Table Name In Stored Procedure Got Error

May 13, 2008

ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY]
@STATE varchar(50),@TBLNAME varchar(50)

AS
BEGIN TRANSACTION -- Start the transaction
TRUNCATE TABLE @TBLNAME;
SELECT
t1.Date_Taken as 'DATE', t1.Time as 'TIME',
t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL'
FROM dbo.SEL t1 INNER JOIN dbo.station_info t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.STATE=@STATE
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time

-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT -- Success! Commit the transaction

Error said Incorrect syntax near '@TBLNAME'.

View 8 Replies View Related

Passing A Table As An Input To Stored Procedure

Jun 18, 2008

Hi everyone,

Is that possible to passing a table as an input to Stored Procedure?

Thanks in advance

View 1 Replies View Related

STORED PROCEDURE - Passing Table Name As A Parameter

Nov 29, 2005

I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)

View 15 Replies View Related

Passing Table Name And Order By Parameter To Stored Procedure

Jul 27, 2007

can i pass the name of the table and the "order by" column name to stored procedure?
 i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?

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

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem

Mar 27, 2006

Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View 3 Replies View Related

Passing Object Names To Stored Procedures

May 31, 2001

Can anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?

Below is my poor effort at the SQL.

Thanks.

CREATE PROCEDURE InsertOrderLine

@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)

AS

DECLARE @SQL VARCHAR(1000)

SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'

GO

View 1 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

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

Variable Table Name In A Stored Procedure

Jul 20, 2005

Hello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings

View 3 Replies View Related

T-SQL, Stored Procedure Using A Variable Table Name

Aug 24, 2007

I need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.

For example, earlier in the program I construct the table name using this statement:

@tablename = 'OC_TEMPLATE_' + @FORM. The resulting table name may by something like: OC_TEMPLATE_101

Now I need to use this table name in a statement like this:

SELECT ITEM FROM @TABLENAME WHERE DEX = @REC

If I use code like this, I can get a result, but I can't store it in a variable:

EXECUTE ('SELECT ITEM FROM ' + @TABLENAME + ' WHERE DEX = ' + @REC)

Running this give me a value of 309 in the results pane in Management Studio. 309 is the value I want, but I want to now store that in a variable called @ITEM.

I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item. I can't figure out how to configure the proc to give me the output parameter.

Any ideas?

T

View 6 Replies View Related

Table Variable In Stored Procedure - Am I Doing This Right?

Oct 31, 2007



I am working on an mailing list program and I am trying to create a preview option for the sender (to preview their message prior to sending)

I am new to .net and sql but I thought it might be best to do this with table variable inside a stored procedure.. I just do not know how to insert the data into the table variable and select that data at the same time, to return it back to a preview.aspx page.

Please forgive my ignorance..

here is my stored procedure..

ALTER PROCEDURE [dbo].[proc_MessagePreview]

@Subject varchar(150),

@EmailMessage varchar(max)

AS

BEGIN

DECLARE @MessagePreview Table(Subject varchar(150), EmailMessage varchar(max))

INSERT INTO @MessagePreview (Subject, EmailMessage)

VALUES (@Subject, @EmailMessage)

SELECT Subject, EmailMessage FROM @MessagePreview

END


any suggestion or help to point me in the right direction is appreciated, thanks!

View 3 Replies View Related

Passing Table Names To Table Valued Functions

May 25, 2008

Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?

For example, a simple stored procedure is this:

CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN

Execute('Select top 10 * from ' + @tableName + ';')

END

I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?

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

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

Feb 8, 2008

Hi

I've used a temporary table in the stored procedure

I've created it as DECLARE @Temp TABLE (id INT)

in select clause I've used this temp table through the joins..

But I've also used a varchar variable to store my criteria...

which I'm building in the stored procedure

so inorder to use it in the where clause I used

EXEC ('SELECT ....................


FROM @Temp T

LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )


Unfortunately this is not working.
Giving the errror


Must declare the variable '@TempT'.

I had to use Temporary table using #, which I feel is really waste of memory...

Is there a way by which I can use my Table variable in the EXEC statement.

Thanks In advance

View 4 Replies View Related

Variable Table Names

Jan 29, 2001

Can I do something like the following with SQL Server?

DECLARE @Table_Name varchar(100)

SET @Table_Name = 'Accounts'

SELECT * FROM @Table_Name

View 2 Replies View Related

Variable Table Names In DTS Package

Jul 20, 2005

I am attempting to export data on a daily basis via DTS to an XLspreadsheet. I would either like to: a) have a separate worksheet inthe spreadsheet for each export or b) a completely differentspreadsheet for each export. Whenever I attempt to use a variable asthe name of my table in the DTS package, it will not let me.Constructing a string and attempting to execute it also fails,although both of these work in query analyzer. Any advice?Peter

View 1 Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Jun 12, 2006

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View 3 Replies View Related







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