June 26, 2003 at 3:25 pm
Hello All,
I'm try to create a stored procedure in a MSS 2000 SP3 database.
Brief desc of the app:
I have a table which stores a unique record. In the application users are allowed to create sql statements(basically where clause agaist the main table). The syntax for the sql statements are stored in tables pertaining to various subjects like pricing. The users can assign various prices depending on whether a loan meets certain syntax. A loan can qualify for multiple prices based on syntax. The deveopers have assigned a sequence arrangement which tells the app which syntax has priority. I've created a cursor to get all of the prices by grid_name. Now the cursor grabs the first price sequenced number 1, I need check to see if the loan qualifies for the syntax used for the price, if it does I need to grab the price and I'm done, if it doesn,t I need to move one to the next price in the grid and compare the syntax. If I can just get it to test and populate a yes/no variable I'm golden.
Here is an sample of what I've tried and could not get to work:
@LNID
is the loan_id from main the table
@psyntax is the syntax I grabbed from the pricing grid using a cursor
Example
SELECT @ls_syntax = 'y '
from v_loan_and_loan_shipped
WHERE loan_id = @LNID
and @psyntax
Does not seem to recognize the syntax in the variable,
I've also tried
BEGIN
SET @cprice_sql = 'select ' + @ls_syntax + '=' + '''Y''' +
' from v_loan_and_loan_shipped where loan_id = '+ @LNID +
' and (' + @psyntax + ') '
EXEC sp_executesql @cprice_sql;
END
Any suggestions??
June 26, 2003 at 4:12 pm
Try executing the below statement before executing the dynamic sql.
print @cprice_sql
The above statement will allow you to preview the sql before it is executed.
Also, if @LNID is a non-string datatype you will need to convert it to a varchar using code similar to the code below in order to concatenate it into a string.
+ convert(varchar(50), @LNID) +
instead of just
+ @LNID +
--------------------------------------
Hello All,
I'm try to create a stored procedure in a MSS 2000 SP3 database.
Brief desc of the app:
I have a table which stores a unique record. In the application users are allowed to create sql statements(basically where clause agaist the main table). The syntax for the sql statements are stored in tables pertaining to various subjects like pricing. The users can assign various prices depending on whether a loan meets certain syntax. A loan can qualify for multiple prices based on syntax. The deveopers have assigned a sequence arrangement which tells the app which syntax has priority. I've created a cursor to get all of the prices by grid_name. Now the cursor grabs the first price sequenced number 1, I need check to see if the loan qualifies for the syntax used for the price, if it does I need to grab the price and I'm done, if it doesn,t I need to move one to the next price in the grid and compare the syntax. If I can just get it to test and populate a yes/no variable I'm golden.
Here is an sample of what I've tried and could not get to work:
@LNID
is the loan_id from main the table
@psyntax is the syntax I grabbed from the pricing grid using a cursor
Example
SELECT @ls_syntax = 'y '
from v_loan_and_loan_shipped
WHERE loan_id = @LNID
and @psyntax
Does not seem to recognize the syntax in the variable,
I've also tried
BEGIN
SET @cprice_sql = 'select ' + @ls_syntax + '=' + '''Y''' +
' from v_loan_and_loan_shipped where loan_id = '+ @LNID +
' and (' + @psyntax + ') '
EXEC sp_executesql @cprice_sql;
END
Any suggestions??
[/quote]
Edited by - kferrier on 06/26/2003 4:15:29 PM
Edited by - kferrier on 06/26/2003 4:16:55 PM
Edited by - kferrier on 06/26/2003 4:17:41 PM
June 26, 2003 at 4:21 pm
I don't have your schema, so here's a NORTHWIND example of using sp_executesql with:
- a dynamically constructed SQL string (equivalent to tacking @psyntax on the end)
- an input parameter (@EmployeeID being equivalent to your @LNID)
- an output parameter (@ls_syntax)
Hope it helps.
USE Northwind
DECLARE @StaticSQLString NVARCHAR(500)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ls_syntax CHAR(1)
DECLARE @EmployeeID INT
SET @EmployeeID = 1
SET @StaticSQLString =
N'SET @ls_syntax = ''N'' SELECT @ls_syntax = ''Y'' FROM Employees WHERE EmployeeID=@EmployeeID'
--
-- Example 1: Should be true
--
SET @SQLString = @StaticSQLString + N' AND FirstName = ''Nancy'''
EXEC sp_executesql @SQLString, N'@EmployeeID INT, @ls_syntax CHAR(1) OUTPUT', @EmployeeID, @ls_syntax OUTPUT
PRINT @ls_syntax
--
-- Example 2: Should be false (Smith is not the employee's last name)
--
SET @SQLString = @StaticSQLString + N' AND FirstName = ''Nancy'' AND LastName = ''Smith'''
EXEC sp_executesql @SQLString, N'@EmployeeID INT, @ls_syntax CHAR(1) OUTPUT', @EmployeeID, @ls_syntax OUTPUT
PRINT @ls_syntax
--
-- Example 3: Should be true
--
SET @SQLString = @StaticSQLString + N' AND FirstName IN (''Nancy'',''Wendy'',''John'')'
EXEC sp_executesql @SQLString, N'@EmployeeID INT, @ls_syntax CHAR(1) OUTPUT', @EmployeeID, @ls_syntax OUTPUT
PRINT @ls_syntax
Cheers,
- Mark
June 27, 2003 at 6:49 am
DECLARE @ok char(1)
SET @ok = 'N'
SET @cprice_sql = 'select @ok=''Y'' from v_loan_and_loan_shipped where loan_id = '+ @LNID + ' and (' + @psyntax + ') '
exec sp_executesql @cprice_sql,N'@ok char(1) output',@ok output
@ok will contain Y/N depending on whether select finds a record
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply