Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Using Stored Procedure Arguments

    Hello. I am pretty new to SQL Stored Procedure, and ran into a problem. I am guessing there is a simple solution to this, as I am self-teaching myself this stuff.

    I currently have a procedure that creates a new table, by reading in columns from a variety of Linked Servers (ODBC connection to to Sybase databases)

    I will want to generalize a procedure by setting as arguments the ODBC connection name and the name of the new table that will be created ("CREATE TABLE") so I can pass these in.

    As a test of this idea, I created this as a simple procedure, but I get a syntax error when I try to Check Syntax this. Do I have to do something else when I reference the string "argu1" to specify a new table name, or an OPEN QUERY Linked table name? Thanks and Happy New Year!

    CREATE PROCEDURE gis.pr_PARCEL_TEST( @argu1 char(25)) AS
    CREATE TABLE @argu1
    GO

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    you need to use dynamic sql ..

    try this:

    create PROCEDURE gis.pr_PARCEL_TEST( @argu1 varchar(25)) AS
    begin
    declare @sql nvarchar(100)

    if not exists(select * from sysobjects where name =substring('a (t int)',1,patindex('% %','a (t int)')))
    begin
    set @sql='CREATE TABLE '+ @argu1
    print @sql
    exec sp_executesql @sql
    end
    else
    begin
    print 'Table with specified name already exists'
    end
    end
    GO
    Last edited by harshal_in; 01-02-06 at 02:55.

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    Thanks, Harshal, This is exactly what I needed!!! Appreciate your help.
    The learning process is continuing. ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •