Pass &<tablename&> As Parametre In StoredProcedure

Feb 15, 2008

Hi Experts,

I m using a stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[SP_Table_SELECT]
(
    @TableName VarChar
)

AS

set nocount on
set ansi_warnings off


DECLARE @sql varchar(2000)


SET @sql = '
SELECT
*
FROM (' +

@TableName + ')'


EXEC (@sql)



when I run It it shows return value = o and Query Completed with Errors

There are data in My Table.
 
Help Plzzzz!!!!!! 

View 7 Replies


ADVERTISEMENT

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

How To Pass Pass The Parameter In SQL Command In SSIS Package

Jul 31, 2006

Hi

   We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

 

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )



 

Query 2: (look up )

 


SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')  AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')



 

please anyone give the sample control flow  and how to pass the parameter?

 

Thanks & regards

Jeyakumar.M

 

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

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

StoredProcedure

Feb 12, 2008

Hi all,
I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me.
Thank You

View 1 Replies View Related

StoredProcedure

Aug 27, 2004

Hi
I use the next StoredProcedure in Access, Inserts Users to tblUsers:
INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments )
VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);

How do i have to write it in SQL?
Thank's.

View 1 Replies View Related

StoredProcedure

Mar 12, 2008

Hi

I am New to Sql Server. Now i have to write two stored procedures.

Here are my requirements. If any one help please.

1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:



Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'



So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:



select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'



So for this one, you would select where underwriterName='John Brennan'.


For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.

Tables for this storedprocedure:

Table Name:mnetwork..unw_Nola
Table Fields:LoanID,ConditionSet,Status,Revised,RevisedBy,PDFNOLA,MonthlyIncome,DocExpDate,Notes,
Underwriter,rowguid,ApprovedDate.MovedToCentera,FK_UserID,RecordDate

TablName:mnetwork..unw_LoginLookup
Table Fields: [LoginLookupID], [LoginName], [UnderwriterName], [FirstName], [LastName], [Title], [Signature], [Address1], [Address2], [Addr1], [Addr2], [City], [State], [Zip], [Phone], [Phone2], [Fax], [Email], [DefaultSet], [rowguid], [FK_UserID], [RecordDate], [Createdby], [LastUpdated], [UpdatedBy]





2)1. Block this loan

2. Grant me access to this blocked loan.

So, I need you to write either one or two stored procedures that will accomplish the following:

When a loan needs to be blocked, it needs to be added to the mnetwork..sec_BlockedLoans table

When a person needs access to that loan, their username needs to be added to the mnetwork..sec_LoanAccess table.

Tables:
1) SELECT [LoanID], [Username], [Grantor], [GrantDate] FROM [mnetwork].[dbo].[sec_LoanAccess]
2) SELECT [LoanID], [Added] FROM [mnetwork].[dbo].[sec_BlockedLoans

any one can help to write these stored procedure.

Thanks,
JT

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

StoredProcedure Return Value

Jun 27, 2006

I am using SQL Server 2005 now and I have a table with following columns.
ID, FirstName, LastName, Email
"ID" is the primary key (int) and is set auto generated (1 increment)
I have a StoredProcedure to insert a new record.
CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO
I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
 

View 3 Replies View Related

StoredProcedure In A Join

Aug 22, 2007

Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:Luca de Angelis
7 -- Create date: 22/08/2007
8 -- Description:Inserimento dei dati contabili nella tabella di log
9 -- =============================================
10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili
11 -- Add the parameters for the stored procedure here
12 @id_gestore tinyint
13 AS
14 SET NOCOUNT ON
15 BEGIN TRANSACTION
16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
17 SELECT CEL_traffico_temp.numero_telefonico
18 , CEL_traffico_temp.anno
19 , CEL_traffico_temp.mese
20 , @id_gestore as id_gestore
21 , 1
22 FROM CEL_traffico_temp
23 INNER JOIN
24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella
25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
26
27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
28 SELECT CEL_traffico_temp.numero_telefonico
29 , CEL_traffico_temp.anno
30 , CEL_traffico_temp.mese
31 , @id_gestore as id_gestore
32 , 2
33 FROM CEL_traffico_temp
34 INNER JOIN
35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella
36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
37
38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
39 SELECT CEL_traffico_temp.numero_telefonico
40 , CEL_traffico_temp.anno
41 , CEL_traffico_temp.mese
42 , @id_gestore as id_gestore
43 , 3
44 FROM CEL_traffico_temp
45 INNER JOIN
46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella
47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
48
49 IF @@error <> 0
50 BEGIN
51 ROLLBACK TRANSACTION
52 END
53 ELSE
54 BEGIN
55 COMMIT TRANSACTION
56 END
  Help me please...

View 2 Replies View Related

IF NOT EXISTS StoredProcedure

May 14, 2008

Visual Studio 2008 Code VB
 I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS .  With the below code I can update the table, however, you can not add additional rows to the table.
Could someone tell me what is wrong, or how to fix it?
 
Thanks! losssoc  ALTER PROCEDURE dbo.CaseDataInsert
 
@ReportType varchar(50),@CreatedBy varchar(50),
@OpenDate smalldatetime,@Territory varchar(10),
@Region varchar(10),@StoreNumber varchar(10),
@StoreAddress varchar(200),@TiplineID varchar(50),
@Status varchar(50),@CaseType varchar(200),
@Offense varchar(200)
 
AS
BEGIN
IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID,
@Status,@CaseType,@Offense)
 
END
 

View 12 Replies View Related

How To Get Results From A Storedprocedure

Jun 11, 2005

To all,
I looked at the MS-SQL pubs sample database and execute the example
stored procedure reptq2 and I got 17 results set back. Where can I find
an example using Visual Studio DataGrid or any means to get all these
results from this SP.

Thanks,


Frank

View 3 Replies View Related

Use C# Through ADO Execute StoredProcedure

Apr 16, 2006

Hi
public static void ExecuteStoredProcedure(string SPName, ref ArrayList Parameters)        {            object result=null;            ADODB.Connection Connection = new ADODB.Connection();            ADODB.Command Command = new ADODB.Command();            Command.ActiveConnection = Connection;            Command.CommandText = SPName;            Command.CommandType = CommandTypeEnum.adCmdStoredProc;
            if (Parameters != null)            {                for (int i = 0; i < Parameters.Count; i++)                {                    Command.Parameters.Append(Parameters[i]);                }            }
            try            {                Connection.Open(ConnectionString, "", "", 0);                Command.Execute(out object RecordAffected, ref object parameters, int options ) ;//the second parameter what mean? how set it?            }            catch (Exception ex)            {                throw ex;            }            finally            {                Connection.Close();            }
        }
Thanks

View 2 Replies View Related

StoredProcedure With Parameters

May 8, 2008

Hi,
I want to know how to write stored procedure with parameters. And i want to compare this parameters.

I have DropDownList and RadioButtonList in my Web Application.

How to write Procedure passing this Two control parameters.(DropDown and RadioList).

In RadioButtonList having 5 selections.

If selection 1 happens

-- some condition

if selection2 hapens
-- some condition

similarly 3,4,5

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

How to write conditions in storeprocedure.

Please help me i am not having exp on storedprocedure.

Thanks


View 1 Replies View Related

Storedprocedure Not Updating The Row

Mar 23, 2008

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE [dbo].[usp_CustomerDetails]

(@Number varchar(30),
@Name varchar(30),
@City varchar(20),
@SSN varchar(20),
@CustomerID int)

AS
BEGIN
IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number)
BEGIN
UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID
END
ELSE
BEGIN
print 'CANNOT UPDATE'
END
END

This my storedproc.
My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number
Then the update should take place.

but if any other row other than CustomerID=1 having name=@name and number=@number
Then the update is should not take place.

but The above stored procedure is not working like that.

so please some one help me with this.
Thankyou
Ramya.


View 4 Replies View Related

StoredProcedure - Cashed

May 11, 2008

Hello ,


When I read about Stored Procedure , I read this Topic


"
First, after SQL Server parses and compiles a stored procedure, it caches
the execution plans in its procedure cache.



I want to know what does it mean about ProcedureCache ??????

another question is :

what is the situations when SqlServer doesnot resuse the StoredProcedure in the ProcedureCache and it must recomplie it again ???


thanks

View 4 Replies View Related

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

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

Ouput Parameter In &#34;select * From @tablename&#34;

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

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







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