Table Parameter In SQL 7

Jan 4, 2002

How do I pass a table name as a parameter to a stored procedure in SQL 7?

Thanks

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Table Returning Function With Input Table Name As Parameter

Nov 19, 2014

I'm using SS 2012.

I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.

In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.

The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.

This means switching to the multi-line function, which I will if I have to, but those are more tedious.

Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?

A simple example of what I'm trying to do is below:

Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)

Error I get is:

Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".

If I use "table" as the parameter type, it gives me:

Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".

The input table can have several thousand rows.

View 9 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

Table As Parameter

Jan 11, 2008

Hello
I have aspx page and I need to insert 15 rows to a table so am thinking of passing them as table to  my sp
but I dont know how to use table as parameter in the sql2005 so PLs help me
 
thanks all

View 4 Replies View Related

How To Use Table Name As Parameter??

Jul 31, 2001

When I execute this sp like this:

exec AddNewSKUsToExistingStyle 'table1'

I am getting the following error: (even though I have table1 in db).

Server: Msg 208, Level 16, State 1, Procedure AddNewSKUsToExistingStyleTest, Line 6
Invalid object name '@table_nm'.

Any ideas on how to use table name as parameter??
Thanks.


script for sp:
-----------------------------------------------------------------------------------------
CREATE Procedure AddNewSKUsToExistingStyle

@table_nm varchar (20)

as
Set nocount on
/* Step No. 1 */
Update site_product_xref
Set Site_Product_End_dt = '12/31/09'
--select sk.eb_style_id, sk.eb_sku_id, spx.Site_Product_Id
from [@table_nm] c inner join Book b
On (c.book_id = b.book_id)
Inner Join Size_Color SZ
On (SZ.EB_Style_Id = c.EB_Style_Id)
Inner Join EB_SKU SK
On (C.EB_Style_Id = SK.EB_Style_Id
and sz.color_id = sk.color_id
and sz.size_id = sk.size_id)
Inner Join site_product_xref spx
On (sk.eb_sku_id = spx.eb_sku_id
and spx.Site_id = 1)
where spx.Site_Product_start_dt < b.live_dt

View 3 Replies View Related

Table Name As A Parameter

Dec 16, 2004

I have to create a tabla with a different name each time. I have a stored procedure to do so like the following. Step 1 works fine but not step 2.
The table name cannot include a parameter.
How can I solve this?
Any help on this will be appreciated.

CREATE PROCEDURE NewTable
@UserName as varchar
AS

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE_' + @UserName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)


CREATE TABLE [dbo].[TABLE_' + @UserName + '] (......

View 2 Replies View Related

T-SQL: Is It Possible To Pass A Table As Parameter?

Dec 7, 2007

Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: -    CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2)    AS    BEGIN        DECLARE @name TABLE             (column list)        etcPerfect: but I need to be able to have another [recursive] procedure process this table.  How do I make it available to the second procedure?   If I write     EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced.  Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'".    If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure?  Do I pass a cursor to it?  Or is it quite impossible?Regards, Robert Barnes.      

View 6 Replies View Related

Query Table With Same Parameter

Mar 4, 2008

Im struggling with this, I think its probably quite simple. I have a table that looks like this:



RecordId
PictureId
KeywordId

111
212
313
421
522
623
725
817
932
1044
 
I want to be able to run a query for a distinct listing of PictureIds that have KeywordId=1 and Keyword=2. When trying a simple query like:select PictureId from PicturesKeywords where (keywordid=1 and keywordid=2) group by PictureId
It obviously returns nothing. What am I doing wrong?

View 12 Replies View Related

Pass Name Of Table As Parameter To UDF

Apr 12, 2008

Hi Friends,
Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function.
Any help would be appreciated.
Fazal.

View 4 Replies View Related

Table Name As A Parameter....URGENT!!

Aug 13, 2001

create Procedure AddNewStyle @table_nm varchar (20)

declare @string varchar (5000)
select @string='Update product
Set Product_End_dt = '12/31/05'
from ' + @table_nm + ' c inner join Booktable b
On (c.book_id = b.book_id).............

exec (@string)
--
I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help!
Reddy.

View 2 Replies View Related

Passing A Table Name Through A Parameter

Jul 2, 2004

Hi there,

I have this problem..

I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....

Can someone help me??

Thanks!!

View 1 Replies View Related

Passing A Table Name Through A Parameter

Jul 3, 2004

Hi there..

I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name.
MS SQL is not accepting it because it is asking me to declare it... can someone help me??

Thankyou

"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0)
AS
BEGIN
DECLARE @TB varchar(30)
SET @TB = @tablename
DECLARE @SR decimal(9,0)

SET @SR= (
SELECT MAX(SysRef)+1
FROM @tablename )

RETURN(@SR)

END

"

View 1 Replies View Related

UDF - Can Pass A Table As Parameter?

Jan 20, 2014

I have several tables which are all identical in columns and datatypes. I want to create a function which does some calculations on this tables.

Since I just want to create one function I Need to specify the table on which the function should work on. That means i want to pass the table Name as a Parameter to the function.

Is that possible?

View 6 Replies View Related

Table Name As Function Parameter

Mar 2, 2008

I'm trying to write a function that I can run with passing the table name as a parameter. I want to return an integer. The tables wll be differnet types (different colums) but they all have a similar field that i want to get a count of. Someting alng the lines of this:

Create FUNCTION [dbo].[fn_GetItemsToPromote]
(
@TableName nvarchar(100)
)
RETURNS int
AS
BEGIN
Declare @SQL nvarchar(500)
return select count(*) from [@TableName]where promote = 1
END

It doesnt like the @TableName. Can anyone show me how to do this correctly?

Thanks!

View 4 Replies View Related

Select With Table Name Parameter

May 16, 2007

Hy , I'he made a stored procedure with 1 parameter @cTableName and I want to return data from that table: select * from @cTableName , that is all. But it does not work. When I execute the stored procedurere I want to send the name of the table : execute MySP "MyTable". Is it possible? Thank you!

View 6 Replies View Related

How To Sort A Table With A Parameter

Jan 8, 2007

I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate.

I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate).

Then I tried to set the sorting

View 6 Replies View Related

Filtering Table Name With Parameter

Apr 2, 2006

<br><br>I obtain
table names from a database and pass them to a dropdownlist. Based on
user selection, I want to pass each table name to a query.Here is an
extract from my code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="select * from @dDTable">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="dDTable" PropertyName="SelectedValue" DefaultValue="product"
Direction="InputOutput" Size="15" Type="String" />
</SelectParameters>
</asp:SqlDataSource>I keep getting this error: Must declare
the table variable "@dDTable".Please does anyone knows the best way to
go about this?

View 3 Replies View Related

SqlDataSource Using A Type Parameter Of Table

Oct 8, 2006

I am not sure (and I've been known to overlook things) if it is possible to use a Table parameter type as parameter to a stored procedure?  Is it possible? I would prefer not to create a SQL batch statement calling an stored proc w/ one parameter over and over. Instead i'd rather just create the stored proc to take a table parameter and pass the table into the stored proc.  I know I could use a CSV string of IDs instead of a table...  or I could create a big SQL batch and use that.  But I'd rather not. Could anyone provide some guidance? Thanks!Joe

View 2 Replies View Related

Table Data Type As Parameter

Aug 24, 2007

How can I use Table Data Type as parameter for Stored Procedures and Functions.

View 1 Replies View Related

Function That Take A Table As Input Parameter

Feb 26, 2008

hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance

View 2 Replies View Related

Problem With Virtual Table As Parameter

Jul 23, 2006

Hi all,

here is what i have so far

set @fld_name = 'sales_vol'
set @tbl_name = 'tbl_sales'

create @tbl table (val int, rank int identity(1,1))
---
I want to insert values of colum 'sales_vol' into the 'val' of the table 'tbl_sales'

i tried this

execute sp_executesql
n'insert into @t(value) select @fld_name from @tbl_name'

---
sql analyser keeps telling me : "Must declare the variable '@t'."

Can I do this? If not, is there another way?

Thank you.

---
here is a sample data for 'sales_vol'
sales_vol
38
217
10
95
34
7
186
39
81
80
31
67
60
5
41
97
259
14
116
-----

View 1 Replies View Related

Stored Proc Parameter For Table Name

Sep 29, 2004

Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc

CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO

NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.

Is it possible to use a parameter as a table name? OR is there another way to do this?

Thanks in advance for your help!

View 3 Replies View Related

Passing Table Name As Parameter To Function

May 24, 2008

Hi all.

I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.

Now to my problem:

I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.

Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.

All ideas are welcome. Thanks.

View 1 Replies View Related

Table Parameter In Stored Procedure?

Jun 2, 2012

I am Creating a Procedure in SQL2000 but founding an error.

Procedure:
CREATE PROCEDURE Itmopbalance
@myfitemcode int,
@ttype char(1),
@myfyear char(9),
@mydatefrom2 datetime,

[code]....

View 3 Replies View Related

Delete Table Based On 2 Parameter

Jun 24, 2014

I am getting runtime error for the below simple execution of sp even I tried casting the @dt to varchar still getting the same error. I want to delete the table based on the 2 parameter ...

1. Table Name first parameter
2. InCondition is the column name which of type datetime

CREATE PROCEDURE dbo.[DeleteTable](@InTblName NVARCHAR(250),@InCondition NVARCHAR(250))
AS
BEGIN
DECLARE @DeleteSQL NVARCHAR(250)
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + @Dt+ ''''
SELECT(@DeleteSQL)
EXECUTE sp_executesql @DeleteSQL
END

I have corrected the code now, I am not getting the output it is throwing error at run time.

EXEC [DeleteTable] 'TABLE_NAME','COLUMN_NAME'

Where column_name is of datatype datetime

Msg 241, Level 16, State 1, Procedure spDeleteTable, Line 8
Conversion failed when converting date and/or time from character string.

View 12 Replies View Related

Parameter As Table Column Values

Sep 21, 2007

can we define a stored procedure parameter which holds a table's column values?
And can we loop in it in a stored procedure?

If yes, could you give a little sql code sample?

Thanx

View 3 Replies View Related

Table Name As An Input Parameter To A Procedure

Feb 25, 2008

Hi
I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30)

)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
DECLARE @SQL1 VARCHAR(2000)
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
EXEC(@SQL1);
Declare @nextCode varchar;
DECLARE @SQL2 VARCHAR(2000)
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
EXEC(@SQL2);
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;


END
GO
Can someone correct me here
Thanks

View 4 Replies View Related

Function Call With Table As Parameter

Jul 23, 2005

Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.

View 4 Replies View Related

Pass Table As A Parameter To A Function

Jul 25, 2007

Hi Friends,Is it possible to pass a table as a parameter to a funtion.whos function declaration would look some thing like this....ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)my problem is: i have to access a temporary table created in an SP ina functionALTER PROCEDURE MySPBEGIN....DECLARE @TmpTable TABLE(...)....TempFunction(@TmpTable)....ENDThanksArunDhaJ

View 12 Replies View Related

Passing Table/column Name As Parameter

Jul 20, 2005

I know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou

View 2 Replies View Related

Function That Take A Table As Input Parameter

Feb 27, 2008

i want to make a function that will take a table as input parameter. this table will be the output of a stored procedure. while i were writing the function i have an error and when i read about it i found that i can not send a table as input parameter to a function till i create a new TYPE of this table with its columns and data types as UDT but i found that sql server 2005 does not support the type 'table'...
my question now is it possible technically to make this function? is it possible to write something like that :

SELECT dbo.MyFunction(exec dbo.MystoredProc)

and in my function i am using CLR-Integration as this :
create function MyFunction
(
@TempTable table
(
ContractID int,
ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount money,
CurrencyID nchar(3),
DateStart smalldatetime,
DateEnd smalldatetime,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30)
)
)
returns table( ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount decimal,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30),
[Year] int,
[Month] int,
DomesticAmount decimal
)
as external name [AssemblyName].[PathOfTheFunctionInTheAssembly].[FunctionNameInAssembly]

please help me in this code as i need it urgently...
thanks in advance,
best regards,
Moustafa

View 9 Replies View Related

Transact SQL :: Using A Parameter To Create A Table?

Jul 21, 2015

I create a script like below:

GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectID  AS NVARCHAR(128),
@TableName  AS NVARCHAR(128);
SET @ProjectID = N'EPA';

[code]....

The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'

how to debug?

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







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