dynamic query

Good Day to all,
I just started using SQL Server 2005 and didn't have any experience with other DB...
Is it possible to make a dynamic query? I would like to make a stored procedure where the table name from where it will select/insert/update data is user inputed... if it's possible, can someone please guide me.. thanks in advance...

example:

@.TableName VARCHAR(20),

SELECT * FROM @.TableName

is it possible?

It sure is possible. Take a look in Books Online at EXEC and sp_executesql

These allow you to build dynamic strings of sql and execute them:

eg

SET @.sql = 'SELECT * FROM ' + @.TableName

EXEC(@.sql)

HTH

|||

Be aware of:

http://www.sommarskog.se/dynamic_sql.html

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||thanks for the help... i manage to use the dynamic sql at SELECT but im having problem with the INSERT function concerning the DateTime... here's the SP

@.s_SalesInvcCode CHAR(9),

@.dt_SalesInvcDate SMALLDATETIME,

@.i_CstmrID INT,

@.dec_SalesInvcTotal DECIMAL(14,2),

@.i_LocationID INT,

@.i_CashRegID INT,

@.i_UserID INT,

@.b_SalesInvcIsVoid BIT

AS

DECLARE @.StoreTable nvarchar(70)
SET @.StoreTable = (SELECT s_SalesTable

FROM Tbl_STORE_StoreInfo)

DECLARE @.sql nvarchar(4000)

SET @.sql = 'INSERT INTO ' + @.StoreTable +' ( [s_SalesInvcCode],

[dt_SalesInvcDate],

[i_CstmrID],

[dec_SalesInvcTotal],

[i_LocationID],

[i_CashRegID],

[i_UserID],

[b_SalesInvcIsVoid] )

VALUES ( '+ @.s_SalesInvcCode +',N'+
convert(nvarchar(25),@.dt_SalesInvcDate) +','+
convert(nvarchar(10),@.i_CstmrID) +','+

convert(nvarchar(16),@.dec_SalesInvcTotal) +','+
convert(nvarchar(2),@.i_LocationID) +','+

convert(nvarchar(2),@.i_CashRegID) +','+
convert(nvarchar(3),@.i_UserID) +','+
convert(nvarchar(10),@.b_SalesInvcIsVoid) +')'

EXEC(@.sql)

regarding the above SP, i'm having a Error converting data type varchar to smalldatetime. but if i comment out the DateTime in the SP, everything works well. what do you think i'm missing? thanks again in advance for the help

Jay Jose
|||

You can utilize the sp_executesql, here you need not to convert the datatype..

Code Snippet

--...

@.s_SalesInvcCode CHAR(9),

@.dt_SalesInvcDate SMALLDATETIME,

@.i_CstmrID INT,

@.dec_SalesInvcTotal DECIMAL(14,2),

@.i_LocationID INT,

@.i_CashRegID INT,

@.i_UserID INT,

@.b_SalesInvcIsVoid BIT

AS

DECLARE @.StoreTable nvarchar(70)

SET @.StoreTable =(SELECT s_SalesTable FROM Tbl_STORE_StoreInfo)

DECLARE @.sql nvarchar(4000)

DECLARE @.paramdec nvarchar(4000)

SET @.sql='INSERT INTO ' +@.StoreTable+' (

[s_SalesInvcCode],

[dt_SalesInvcDate],

[i_CstmrID],

[dec_SalesInvcTotal],

[i_LocationID],

[i_CashRegID],

[i_UserID],

[b_SalesInvcIsVoid] )

VALUES (

@.s_SalesInvcCode,

@.dt_SalesInvcDate,

@.i_CstmrID,

@.dec_SalesInvcTotal,

@.i_LocationID,

@.i_CashRegID,

@.i_UserID,

@.b_SalesInvcIsVoid)'

Set @.paramdec = '@.s_SalesInvcCode CHAR(9),

@.dt_SalesInvcDate SMALLDATETIME,

@.i_CstmrID INT,

@.dec_SalesInvcTotal DECIMAL(14,2),

@.i_LocationID INT,

@.i_CashRegID INT,

@.i_UserID INT,

@.b_SalesInvcIsVoid BIT'

Exec sp_executesql @.sql,

@.paramdec,

@.s_SalesInvcCode,

@.dt_SalesInvcDate,

@.i_CstmrID,

@.dec_SalesInvcTotal,

@.i_LocationID,

@.i_CashRegID,

@.i_UserID,

@.b_SalesInvcIsVoid

|||

Manivannan.D.Sekaran wrote:

You can utilize the sp_executesql, here you need not to convert the datatype..

Code Snippet

--...

@.s_SalesInvcCode CHAR(9),

@.dt_SalesInvcDate SMALLDATETIME,

@.i_CstmrID INT,

@.dec_SalesInvcTotal DECIMAL(14,2),

@.i_LocationID INT,

@.i_CashRegID INT,

@.i_UserID INT,

@.b_SalesInvcIsVoid BIT

AS

DECLARE @.StoreTable nvarchar(70)

SET @.StoreTable = (SELECT s_SalesTable FROM Tbl_STORE_StoreInfo)

DECLARE @.sql nvarchar(4000)

DECLARE @.paramdec nvarchar(4000)

SET @.sql = 'INSERT INTO ' + @.StoreTable +' (

[s_SalesInvcCode],

[dt_SalesInvcDate],

[i_CstmrID],

[dec_SalesInvcTotal],

[i_LocationID],

[i_CashRegID],

[i_UserID],

[b_SalesInvcIsVoid] )

VALUES (

@.s_SalesInvcCode,

@.dt_SalesInvcDate,

@.i_CstmrID,

@.dec_SalesInvcTotal,

@.i_LocationID,

@.i_CashRegID,

@.i_UserID,

@.b_SalesInvcIsVoid)'

Set @.paramdec = '@.s_SalesInvcCode CHAR(9),

@.dt_SalesInvcDate SMALLDATETIME,

@.i_CstmrID INT,

@.dec_SalesInvcTotal DECIMAL(14,2),

@.i_LocationID INT,

@.i_CashRegID INT,

@.i_UserID INT,

@.b_SalesInvcIsVoid BIT'

Exec sp_executesql @.sql,

@.paramdec,

@.s_SalesInvcCode,

@.dt_SalesInvcDate,

@.i_CstmrID,

@.dec_SalesInvcTotal,

@.i_LocationID,

@.i_CashRegID,

@.i_UserID,

@.b_SalesInvcIsVoid

solved thanks for all the help
sql