Using Parameter In SP As Tablename

Jul 20, 2000

Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:

CREATE PROCEDURE make_table
@tablename varchar(20)
AS
SELECT * FROM old_table
INTO @tablename

I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.

View 1 Replies


ADVERTISEMENT

Declaring A Tablename In A Sproc As A Parameter

Oct 2, 2006

HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition

View 5 Replies View Related

Ouput Parameter In "select * From @tablename"

Aug 30, 2000

Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name


but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.

I tried
select @num = max(field1) from object_id(@name)
and the error was the same

is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg

View 1 Replies View Related

Stored Procedures, Parameter, Tablename

Jun 20, 2006

Hello,

I would like to pass into a stored procedure the name of a table. Following code does not work.
USE pubs
GO
CREATE PROC test1
@tableName varchar(40)
AS
SELECT * FROM @tableName
EXEC test1 @tableName='authors'

I would appreciate it very much if someone could help me with this.

Thanks

View 5 Replies View Related

Problem With Stored Procedure And Tablename Parameter

Jul 20, 2005

helloI am trying to do a procedure that :1 - Take a table name, a field name and a value as parameter2 - check if the fieldname=value in the tablename table3 - returns 1 if so, 0 if notA -I tried :drop procedure checkmytableGOcreate procedure checkmytable@p_table nvarchar(50),@p_field nvarchar(50),@p_value nvarchar(50)asif exists ( select * from @p_table where @p_field = @p_value)return 1elsereturn 0declare@res intexec @res=checkmytable 'mytable' , 'myfield' , '456123'print @resin this case, I always get the error : 'must declare @p_table'B -I trieddrop procedure checkmytableGOcreate procedure checkmytable@p_table nvarchar(50),@p_field nvarchar(50),@p_value nvarchar(50)asreturn exec( 'if exists (select * from' + @p_table + ' where ' + @p_field +' = ' + @p_value + ') return 1 else return 0' )GOdeclare@res intexec @res=checkmytable 'AM_ASSET' , 'ASSET_ID' , '18705'print @resin this case, I always get 0any help ?

View 1 Replies View Related

How To Use @tablename

Feb 12, 2005

i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @tableName but i m unable to do so..
it says u need to define @tablename

heres da code
CREATE PROCEDURE validateChildId
(
@childId int,
@tableName varchar(50),
@fid int output
)
AS
(
SELECT @fid=fid FROM @tableName
where @childId= childId
)

if @@rowcount<1
SELECT
@fid = 0
GO

View 4 Replies View Related

Tablename

Aug 2, 2007

Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank

View 2 Replies View Related

&<Tablename&> . . &<ColumnName&>

Apr 30, 2004

Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working

Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b

Can anyone let me know.

TIA,
Ravi

View 2 Replies View Related

SET IDENTITY_INSERT Tablename ON

Sep 16, 2006

I know there has already been a thread on this, but I want to push some about it.

In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".

That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.

In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.

The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.

Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)

Thanks.

View 4 Replies View Related

SET IDENTITY_INSERT Tablename ON

Nov 15, 2007

I use IDENTITY(1,1)
and the help says:
SET IDENTITY_INSERT tablename ON

But if the table is an variable(virtual) table how to make it work?


Like this:
DECLARE @TEMPtable table ( ID int Identity(1,1), invar nvarchar(255)


It does not work with
SET IDENTITY_INSERT @TEMPtable ON

I get this

'Incorrect syntax near '@TEMPtable '

View 12 Replies View Related

Select * From Tablename Where Column1 In ()

Apr 4, 2008

Hi to all ,           I write the query like belwodeclare @userid nvarchar(20)set @userid ='6,8'print @useridselect * from user_master where user_id in (@userid)Here user_id  is int datatype, when i execute this query then meet the error as  6,8Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value '6,8' to a column of data type int. How to do this.? Anybody answer me,... 

View 5 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned in second

View 1 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column

note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned because max(id_no) column is absent

if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2

this query return that

insqlhelp ---------------
aaa2

(1 row(s) affected)

this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")

other way is

set to any other text variable to table name,
and concetanate to query,
and execute with exec

but in this way you cant set to my int variable

please help me thanks for all

hra

View 1 Replies View Related

Using Cursor Variable As A Tablename

Oct 12, 2005

I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

View 4 Replies View Related

Dynamic Tablename Issue

Apr 1, 2004

Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:

declare c cursor
for
select * from @tablename
open c
fetch c into ....

but I get an error for declare cursor for dynamic tablename,

did sql server has some BIF to evaluate the variable for table name?

thanks for your kindly assistance.

regards,

Stanley Huang

View 4 Replies View Related

How To Get Tablename / ForeignKey And Constraints

Sep 19, 2013

I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints

SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema',
QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table',
C.NAME AS 'Column',
T.NAME AS 'DataType',
C.max_length,
C.is_nullable,
c.precision,
c.scale

[code]...

View 4 Replies View Related

Function With A Tablename As Variable

Feb 7, 2006

Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

end

return @aantal

END

View 2 Replies View Related

Using A Variable For Tablename In Select Statement?

Sep 19, 2007

I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View 1 Replies View Related

Pass &<tablename&> As Parametre In StoredProcedure

Feb 15, 2008

Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT](    @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!! 

View 7 Replies View Related

Can Is Pass Tablename In SQL Stmt As Variable

Jul 1, 1999

I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt


thanks in advance

View 1 Replies View Related

Creating A Tablename Based On A Date

Feb 25, 2002

Hi,

I am new to SQL Server, and this may be a silly problem .. but here goes!

I need to create tables based on the date ie FEB2000 for EOM reports, I thought I may be able to do it by

1) Check if Table Exists, if so fop it
2) Recreate Table
3) Populate it with data

Unfortunately I'm still on step 1 <sad look>

--->

CREATE PROCEDURE TEST AS

DECLARE @TableName varchar(50)

SET @Tablename = 'FEB2000'

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Tablename)
DROP TABLE @tablename
GO

<---

I would of course hand the table name as a variable, but for testing purposes used a set variable.

Thanks in Advance

View 2 Replies View Related

Sql_help Tablename (Wrong Result)

Oct 31, 2006

Hi All,

I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.

But if i see same table through QA using sp_help tablename
then it will show length of my nvarchar column just double.

when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.

i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.

can anybody tell where is the problem exaqctly ?

View 8 Replies View Related

Passing Tablename To Proc From Trigger

Mar 23, 2007

--------------------------------------------------------------------------------

I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.

Can it be done ?

trigger x on table y
for update
as
declare @table_name
select @table_name = get underlying table_name 'y' from somewhere
exec stored proc (@table_name)

where do I find the name of the table the current trigger is defined on ?

thanks

View 3 Replies View Related

Error - Invalid Object Name (tablename)

Apr 1, 2014

i have a application in which i get "invalid object name "tablename" " sometimes. and its will be fixed if i do a IIS restart.

View 3 Replies View Related

ALTER INDEX &&<tablename&&> REBUILD

Aug 30, 2007

I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)


Barkingdog

View 1 Replies View Related

TableName Property In Conditional Multiple Recordset

Dec 29, 2007

Hi everyone,

I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData
(
@a bit
@b bit
)

Select * from Customers

IF(@a=1)
Select * from Products

IF(@b=1)
Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.   

View 3 Replies View Related

Is There Any Performance Loss If I Have All My Columns Preceeded With Dbo.Tablename?

Jan 6, 2006

I am doing a mass update of our SQL script files by adding dbo.to all references to the tables.The code is also adding dbo. in front of existing lines of codethat are like this:SELECT CLIENT.FIRSTNAME,CLIENT.LASTNAMEFROM CLIENT....The mass update is causing this to become:SELECT dbo.CLIENT.FIRSTNAME,dbo.CLIENT.LASTNAMEFROM dbo.CLIENT....My goal is to see FROM dbo.CLIENTbut even the column names are getting the "dbo."added, so my question is if there's any performanceloss or any other side effect if I end up all my columnswith a preceeding "dbo."?Thank you

View 4 Replies View Related

How Can You Use A Variable Tablename And Retrieve The Output From The Insert?

Aug 28, 2007

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.

How can you use a variable table name and retrieve the output from the Insert?


declare @tTestTable varchar (20)

set @tTestTable = 'mis.test_sequence'


--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int

DECLARE @tTemp table (mainpk int)

set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'

select @testsql

EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp

SELECT * FROM @tTemp



Please help
Thanks Tim.

View 3 Replies View Related

Integration Services :: Passing Tablename In Query As Variable?

May 6, 2015

I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.

So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROMĀ  ? ......

Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:

[Task Execute SQL] Error:

Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract

the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.

how to pass a tablename as a variable to a query?

View 5 Replies View Related

The Multi-part Identifier Tablename.fldname Could Not Be Bound.

Jan 14, 2008

can anybody please help me on this error -
The multi-part identifier "tablename.fldname" could not be bound.

update sub_purchase
set sub_purchase.ic_status=0, sub_purchase.qty_bal=sub_purchase.qty_bal+ sub_sales.qty_bal, sub_purchase.qty_issued=sub_purchase.qty_issued-sub_sales.qty_bal

where sub_purchase.item_code=sub_sales.item_code and sub_sales.purchase_id=sub_purchase.purchase_id and sub_sales.sales_id= 1

View 3 Replies View Related

Accessing Data From Basic SQL Select * From TableName WHERE Search Params

Nov 5, 2007

I am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry.
If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem?  My sub that handles the sql command is shown below.
I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String)
Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty
sql = Cmd
'Open the connection to the database and execute he SQL
conn.Open()Dim command As New SqlCommand(sql, conn)
command.ExecuteNonQuery()
conn.Close()
End Sub
 
 

View 6 Replies View Related

How Could Load Results Of [DBCC Showcontig (@TableName) With TableResults] To A Table

Feb 19, 2008

Hi:

how could I load "DBCC showcontig (@TableName) with tableResults" to a table?
I created a table and let insert to it via above "DBCC...."
but problem is the results with "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", or how could I mute this output line for each table ?
thanks
David
--=========================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -------------------- -------------------- ----------------- ----------------- ---------------------- -------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ----------------------
sysproxylogin 37575172 clust 1 0 0 0 0 0 0 0 0 0 0 0 100 0 0 0 0

View 1 Replies View Related

SQL Server 2008 :: Replace Tablename In Multiple Stored Procedures?

Mar 3, 2015

I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.

-- set "Result to Text" mode by pressing Ctrl+T

SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'

[code].....

View 0 Replies View Related







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