User defined function in sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

User defined function in sp_executesql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-16 : 11:21:37

Eden writes "I am trying to run a dynamically built string using sp_executesql. The string contains an inner join with user-defined inline function. My question is: is it possible for sp_executesql to run the string which contains an inline function which has parameters? If yes, how come it doesn't return any record(s). Here's my code:

ALTER PROCEDURE dbo.prcGetChunkTradingSheet
(
@UsrCode char(6),
@CompID tinyint,
@SysCode char(10),
@ChunkSize int,
@PagePosition int,
@SortField nvarchar(25) = N'DocCntr',
@MoreFilter nvarchar(1000) = N'',
@RowCount int OUTPUT
)
AS

SET NOCOUNT ON

DECLARE @SQLString nvarchar(3500)
DECLARE @ParmDefinition nvarchar(3500)

-- Build the SQL String to get the total number of records
SET @SQLString =
N'SELECT @CountOfRows = Count(*)
FROM dbo.tblRelTxnSecurity
INNER JOIN dbo.tblTxnSecurity
ON dbo.tblRelTxnSecurity.SecID = dbo.tblTxnSecurity.SecID
RIGHT OUTER JOIN dbo.tblTxnDocument
RIGHT OUTER JOIN dbcGeneral.dbo.tblMstrCurrency
INNER JOIN dbo.tblTxnAccount
INNER JOIN dbo.tblRelTxnActivity
INNER JOIN dbo.tblRelTxnProd
INNER JOIN dbo.fnEmpDeptAccess(@EmpCode,@InstID,@SystemCode) fnEmpDeptAccess
INNER JOIN dbo.tblTxnTransaction
ON fnEmpDeptAccess.EmpCode = dbo.tblTxnTransaction.Trader
ON dbo.tblRelTxnProd.TxnID = dbo.tblTxnTransaction.TxnID
INNER JOIN dbo.tblTxnProduct
ON dbo.tblRelTxnProd.ProdID = dbo.tblTxnProduct.ProdID
ON dbo.tblRelTxnActivity.TxnID = dbo.tblTxnTransaction.TxnID
INNER JOIN dbo.tblRelTxnAcct
ON dbo.tblTxnTransaction.TxnID = dbo.tblRelTxnAcct.TxnID
ON dbo.tblTxnAccount.AcctID = dbo.tblRelTxnAcct.AcctID
ON dbcGeneral.dbo.tblMstrCurrency.CurrID = dbo.tblTxnTransaction.CurrID
ON dbo.tblTxnDocument.TxnID = dbo.tblTxnTransaction.TxnID
ON dbo.tblRelTxnSecurity.TxnID = dbo.tblTxnTransaction.TxnID
WHERE (dbo.tblRelTxnActivity.DateActivity IS NULL)
AND (dbo.tblRelTxnActivity.ActivityID > 3)
AND (dbo.tblTxnDocument.DocReprintCntr >= 0)
' + @MoreFilter

SET @ParmDefinition = N'@CountOfRows int OUTPUT, @EmpCode char(6), @InstID tinyint, @SystemCode char(10)'

-- Execute the built SQL String to get the total number of records
EXECUTE sp_executesql @SQLString, @ParmDefinition, @RowCount OUTPUT, @EmpCode = @UsrCode, @InstID = @CompID, @SystemCode = @SysCode

DECLARE @InnerTop int
Set @InnerTop = @ChunkSize * @PagePosition

-- Build the dynamic SQL string to get only a chunk of records
SET @SQLString =
N'SELECT * FROM
(SELECT TOP ' + CAST(@ChunkSize AS nvarchar(10)) + N' * FROM
(SELECT TOP ' + CAST(@InnerTop AS nvarchar(10)) +
N' dbo.tblTxnTransaction.TxnID,
dbo.tblTxnTransaction.TxnTypeID,
dbo.tblTxnTransaction.CompID,
dbo.tblTxnTransaction.Trader,
REPLACE(STR(dbo.tblTxnDocument.DocCntr),'' '',''0'') AS DocCntr,
dbo.tblRelTxnProd.ProdID,
dbo.tblTxnProduct.ProdGrpID,
dbo.tblTxnProduct.ProdShortName,
dbo.tblRelTxnAcct.AcctID,
dbo.tblTxnAccount.AcctCntr,
dbo.tblTxnAccount.AcctName,
dbcGeneral.dbo.tblMstrCurrency.CurrCode,
dbo.tblTxnTransaction.ValFace,
dbo.tblTxnTransaction.ValPrinc,
dbo.tblTxnTransaction.DateDeal,
dbo.tblTxnTransaction.FlagCancel,
dbo.tblTxnSecurity.ISIN
FROM dbo.tblRelTxnSecurity
INNER JOIN dbo.tblTxnSecurity
ON dbo.tblRelTxnSecurity.SecID = dbo.tblTxnSecurity.SecID
RIGHT OUTER JOIN dbo.tblTxnDocument
RIGHT OUTER JOIN dbcGeneral.dbo.tblMstrCurrency
INNER JOIN dbo.tblTxnAccount
INNER JOIN dbo.tblRelTxnActivity
INNER JOIN dbo.tblRelTxnProd
INNER JOIN dbo.fnEmpDeptAccess(@EmpCode,@InstID,@SystemCode) fnEmpDeptAccess
INNER JOIN dbo.tblTxnTransaction
ON fnEmpDeptAccess.EmpCode = dbo.tbl

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-16 : 11:47:56

Try it with a simpler query with the udf and post the results of that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources