October 9, 2006 at 3:09 am
Hi,
is there any way to construct a dynamic query and execute it inside a user-defined function?...
Eg:: how can we execute the dynamic query in the below example...
Create Test (@ColumnName As VarChar(20),@UserCode As BigInt
)
RETURNS
nVarchar(4000)
AS
BEGDIN
Declare @Qry VarChar(200)
Set @Qry='Select' + @ColumnName + 'from user_master where user_code=' + Cast(@usercode as varchar)
End
October 9, 2006 at 5:33 am
Daniel,
As per BOL, only extended stored procedures & functions can be executed from within a function. Functions cannot change the state of the database (DML statements), they just act as general function which returns a single value rowset , table valued rowset , scalar value.
Dynamic queries can be easily constructed in stored procedures which will do for it. Here is a simple sp which returns the company name from customers table of northwind database...
Use Northwind
Go
CREATE PROCEDURE dbo.GetCompanyName
(
@strColumnName VARCHAR(128) ,
@strCustomerID NCHAR(5) ,
@strCompanyName NVARCHAR(40) = '' OUTPUT
)
AS
BEGIN
DECLARE @strSQL NVARCHAR(4000)
SET @strSQL = 'SELECT @strCompanyName = ' + @strColumnName + ' FROM dbo.Customers WHERE CustomerID = ''' + @strCustomerID + ''''
EXECUTE sp_executesql @strSQL , N'@strCompanyName AS NVARCHAR(40) OUTPUT' , @strCompanyName = @strCompanyName OUTPUT
END
GO
DECLARE @strCompanyName NVARCHAR(40)
SET @strCompanyName = ''
EXEC dbo.GetCompanyName N'CompanyName',N'LAZYK',@strCompanyName OUTPUT
SELECT @strCompanyName
GO
DROP PROCEDURE dbo.GetCompanyName
--Ramesh
October 9, 2006 at 10:33 pm
Thanks a lot for your guidlines.........
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply