Table Name as SP Parameter

  • How Pass ans Table Name as Table Filed Name as Parameter and do a Simple Query Like

    Select FieldName From TableName




    My Blog: http://dineshasanka.spaces.live.com/

  • hi,

    very simple example to illustrate the point :-

    create procedure generic_select @table_name varchar(255), @field_name varchar(255)

    as

    declare @sqlstring varchar(8000)

    select @sqlstring = "select " + @field_name + " from " + @table_name

    execute (@sqlstring)

    print @sqlstring

    go

    execute generic_select pgr, birth_date

    HTH

    Paul

  • I think you'd need to use dynamic sql - you can pass the table & field names in as a varchar or sysname datatype and use sp_executesql (see BOL) in a stored proc or xp_exec in a UDF, but this would probably be horribly inefficient compared to a normal stored proc.

  • You can create Dynamic SQL statments

    and execute them using EXEC.

    But I am not sure if this is the right way to go about.

    What I feel is that you are trying to write very generalized stored procedures.

    There are problems in using dynamic SQL statements such as SQL INJECTION and moreover your query will not be optimized for faster data access,which is I guess one of the key reasons of creating Stored procedures.

    He who knows others is learned but the wise one is one who knows himself.


    He who knows others is learned but the wise one is one who knows himself.

  • CREATE PROCEDURE [dbo].[FindANDReplaceString]

    @Tablename varchar(20),

    @FieldName varchar(20),

    @FindString varchar(30),

    @RepalceString varchar(30)

    AS

    /*'Select * From Tablename*/

    Update @Tablename Set @FieldName = Replace(@FieldName,@FindString,@RepalceString)

    GO

    Above SP @Tablename ,@FieldName are in correct.Can any body tell me Y




    My Blog: http://dineshasanka.spaces.live.com/

  • quote:


    Update @Tablename Set @FieldName = Replace(@FieldName,@FindString,@RepalceString)

    GO

    Make it

    'Update ' + @Tablename + 'Set ' + @FieldName + ' = ' + 'Replace (' + @FieldName + ',' + @FindString + ',' + @RepalceString +')'

    Basically You need to concatenate it properly.

    [/b]

    Above SP @Tablename ,@FieldName are in correct.Can any body tell me Y


    He who knows others is learned but the wise one is one who knows himself.


    He who knows others is learned but the wise one is one who knows himself.

  • CREATE PROCEDURE [dbo].[FindANDReplaceString]

    @Tablename varchar(20),

    @FieldName varchar(20),

    @FindString varchar(30),

    @RepalceString varchar(30)

    AS

    /*'Select * From Tablename*/

    declare @sqlstring varchar(8000)

    Select @sqlstring = "Update " + @Tablename + " Set " + @FieldName + " = Replace(" + @FieldName + ",'" + @FindString + "','" + @RepalceString + "')"

    Print @sqlstring

    execute (@sqlstring)

    GO

    NOW It's working With the help of u




    My Blog: http://dineshasanka.spaces.live.com/

Viewing 7 posts - 1 through 6 (of 6 total)

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