Post reply

simple store procedure

  • April 5, 2014 at 9:42 am

    #396549

    Hey Guys,

    I am using SQL Ser 2012 and using adventureworks 2012 db.

    I am creating a simple SP wherein I search for a table and if it exists I drop and if not I create

    While doing that I am getting variable error at create and drop statements, need help

    this is the error

    Msg 102, Level 15, State 1, Procedure findtable, Line 17

    Incorrect syntax near '@tablename'.

    and below is the SP

    create proc HumanResources.findtable

    @tablename varchar(20)

    as

    begin

    set nocount on

    declare

    @C as int

    set @C= (select count(*)

    from INFORMATION_SCHEMA.TABLES

    where TABLE_NAME=@tablename)

    if @C = 0

    create table @tablename (table_name varchar(20))

    else

    drop table @tablename

    end

  • April 5, 2014 at 10:22 am

    #1703499

    this may get you started

    IF NOT EXISTS (

    SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[YOURTABLE]') AND type IN (N'U')

    )

    BEGIN

    CREATE TABLE [YOURTABLE] ([num] [int] NOT NULL) ON [PRIMARY]

    END

    GO

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • April 5, 2014 at 3:18 pm

    #1703540

    Hi

    You can use this code for example

    if object_id(@tablename) is not null

    begin

    print 'exists...'

    --do other steps

    end

    else

    begin

    print 'doesn''t exist...'

    --do other steps

    end

    Regards,

    Igor

    Igor Micev,My blog: www.igormicev.com

  • April 5, 2014 at 4:17 pm

    #1703543

    Thank you so much Guys. But I was wondering what is wrong in my version of code?

  • April 5, 2014 at 4:52 pm

    #1703545

    ankit.oza (4/5/2014)

    Thank you so much Guys. But I was wondering what is wrong in my version of code?

    The problem is that you cannot pass sql identifiers such as table names using a string variable, you will have to use the variable to construct a dynamic sql string, then execute it.

    Another problem with your code is that it doesn't check the schema, there can be more than one table with the same name but in a different schema.

    😎

    A quick fix of the code here;

    create proc HumanResources.findtable

    @tablename varchar(20)

    ,@schemaname nvarchar(20)

    as

    begin

    set nocount on

    declare

    @C as int

    set @C= (select count(*) -- [HumanResources].[Employee]

    from INFORMATION_SCHEMA.TABLES

    where TABLE_NAME=@tablename

    and TABLE_SCHEMA = @schemaname)

    if @C = 0

    /* change print to exec */

    print concat('create table ',@tablename,' (',@tablename,' varchar(20))');

    else

    /* change print to exec */

    print concat('drop table ',@tablename);

    end

  • April 5, 2014 at 7:29 pm

    #1703556

    print concat('drop table ',@tablename);

    I just can't help it. CONCAT seems so much less natural and less obvious and more difficult to read than...

    print 'drop table ' + @tablename;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 5, 2014 at 9:59 pm

    #1703559

    Agree with the other responses. Also, if you are using table variables, you do not need to drop and recreate them. They will be destroyed once the stored procedure completes - just as it would for other variables of character or integer data-types.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • April 5, 2014 at 11:31 pm

    #1703563

    Jeff Moden (4/5/2014)

    print concat('drop table ',@tablename);

    I just can't help it. CONCAT seems so much less natural and less obvious and more difficult to read than...

    print 'drop table ' + @tablename;

    The CONCAT is less ambiguous, not for humans but for the server.:cool:

    Consider this code;

    DECLARE @TINT INT = 10 ;

    DECLARE @NUMSTR VARCHAR(10) = '12';

    SELECT

    'Concatination by addition operator' AS TITLE

    , @NUMSTR + @TINT AS RESULT

    UNION ALL

    SELECT

    'Concatination using CONCAT'

    ,CONCAT(@NUMSTR ,@TINT) ;

    Results

    TITLE RESULT

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

    Concatination by addition operator 22

    Concatination using CONCAT 1012

  • April 7, 2014 at 8:28 am

    #1703810

    Thank You All!!:-)

  • April 8, 2014 at 11:03 am

    #1704213

    ankit.oza (4/7/2014)

    Thank You All!!:-)

    I don't think anyone here has provided an example of executing dynamic SQL. There are various approaches and pitfalls, so I'll provide a link the definitive tutotial by Erland Sommarskog.

    http://sommarskog.se/dynamic_sql.html

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply