January 4, 2010 at 7:36 pm
hello all,
I am dynamically building a sql query with values from a table. I am getting an error with this station:
set sql_str = case when @condition then
assign something to this string
end
@condition: a string contains value like : value1 < value2
Is any function like eval() in sql that I can use? thank you.
Sherry
January 4, 2010 at 8:25 pm
It would help if you would show us everything instead of just small pieces. Also, what is the expected results you are looking for in the end?
January 4, 2010 at 11:25 pm
I think I understand what you're after, I also think your approach is wrong. But if you're really stuck, sounds like you need to dynamically work out the dynamic SQL (not sure if that even sounds right).
i.e.
DECLARE @Condition nvarchar(50)
SET @Condition = '1 < 2'--your condition, if true then SQL is "SELECT 1", else "SELECT 2"
DECLARE @SQL nvarchar(4000)
SET @SQL = 'SET @SQL = CASE WHEN ' + @Condition + ' THEN ''SELECT 1'' ELSE ''SELECT 2'' END'
exec dbo.sp_executesql @SQL, N'@SQL nvarchar(4000) out', @SQL out
--your SQL is ready
exec dbo.sp_executesql @SQL
January 5, 2010 at 5:23 am
Con Alexis,
thank you for your reply. I am debugging my code right now . I should post my result later.
Sherry
January 5, 2010 at 6:32 am
New issue, I am testing the script. I defined the condition field to be varchar(500) in the table. I queried out the table into a cursor. Everything works. However, when I FETCH into a variable, if it is empty, it returns false when I use this statement to evaluate it: if @condition = '' or isnull(@condition,'') = ''
what is the value of the @condition?? I declare @condition varchar(500). Please help....
Sherry;-)
January 5, 2010 at 6:41 am
I still think it would help us if you showed us the whole problem. We can't see what you see from here and as much as we try, we really aren't mind readers (even if it seems like we are at times, that is just luck).
January 5, 2010 at 6:59 am
sunpalozzi (1/5/2010)
New issue, I am testing the script. I defined the condition field to be varchar(500) in the table. I queried out the table into a cursor. Everything works. However, when I FETCH into a variable, if it is empty, it returns false when I use this statement to evaluate it: if @condition = '' or isnull(@condition,'') = ''what is the value of the @condition?? I declare @condition varchar(500). Please help....
Sherry;-)
I strongly recommend you do what Lynn says. It sounds like you're going down a bad road. Cursors are probably not the way to solve the problem you're working on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2010 at 7:02 am
Hi
I agree with Lynn about understanding the full problem.
With this in mind and from what I have been able to gather you want to use this one within a T-SQL predicate.
Have you tried this.
[font="Courier New"]CREATE FUNCTION Get_Pos_Sum
(@fundlist varchar(500) = ' ',
@assetlist varchar(900),
@startdate char(10) = ' ',
@enddate char(10) = ' '
)
RETURNS @resultset TABLE
(
FUND_IDVARCHAR (8 )NULL,
..............
..............
WHERE ........
-- This is where I use the contents of the input parameter within the
--prediciate
--Check to see if asset id's have been passed
and ((1 = CASE
WHEN @assetlist =''
THEN 1 ELSE 2
END) or CHARINDEX(psd.asset_id,@assetlist1,1) >0 )[/font]
Although in mine, assetlist is passed in, declaring a @variable and setting it within the procedure should not cause a problem.
Once again, it would be great to understand the full problem.
sincerest regards Steve
January 5, 2010 at 7:03 am
sunpalozzi (1/5/2010)
New issue, I am testing the script. I defined the condition field to be varchar(500) in the table. I queried out the table into a cursor. Everything works. However, when I FETCH into a variable, if it is empty, it returns false when I use this statement to evaluate it: if @condition = '' or isnull(@condition,'') = ''what is the value of the @condition?? I declare @condition varchar(500). Please help....
Sherry;-)
It's NULL.
People can't help you if they can't see what you are doing (or trying to do). As Lynn says above, please post more info.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2010 at 7:33 am
Please copy and paste in sql, so it is easier to read:
declare @sql_str Nvarchar(4000)
declare @mrm_fld_name varchar(25),
@vv varchar(500),
@condition varchar(1000),
@er_msg varchar(2000),
@client_sub_ident int,
@format char(1),
@error_cnt int,
@claim_ident int,
@himp_ident int,
@fld_len int
DECLARE fld_list_cursor CURSOR FOR
select i.MRM_Fld_Name, fr.Valid_value, fr.condition,
fr.error_msg, i.format, i.fld_len
--into temp_odar_validation
from odar o inner join dbo.PLATFORMS p on
o.client_sub_ident = p.client_sub_ident
inner join
dbo.Odar_PlatForm_FLD_Requirements fr on
p.platform_ident = fr.Platform_ident inner join Init_Odar_Req_FLDs i on
fr.Odar_init_Fld_ident = i.ident and fr.mandatory = 1
where o.SENT = 0 and
o.client_sub_ident = 9
group by p.platform_ident, o.CLIENT_SUB_IDENT,
i.ident, i.MRM_Fld_Name, fr.mandatory, fr.Valid_value,
fr.condition, fr.error_msg, i.format, i.fld_len
OPEN fld_list_cursor
FETCH NEXT FROM fld_list_cursor INTO @mrm_fld_name,
@vv, @condition, @er_msg, @claim_ident,
@himp_ident, @format
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql_str = 'INSERT INTO ODAR_DA_ERRORS ( ' +
' [ODAR_Ident]' +
' ,[Clm_aud_nbr]' +
' ,[Procedure_Code]' +
' ,[Orig_Procedure_Code_Modifier_1]' +
' ,[Charged_Amount]' +
' ,[Allowed_Amount]' +
' ,[Paid_Amount]' +
' ,[Audit_Amount]' +
' ,[Coinsurance_Amount]' +
' ,[Deductible_Amount]' +
' ,[Copay_Amount]' +
' ,[First_Date_of_Service]' +
' ,[MRM_Claim_Ident]' +
' ,[Member_Number]' +
' ,[Member_First_Name]' +
' ,[Member_Last_Name]' +
' ,[Error_Type]' +
' ,[Error_Sub_Type]' +
' ,[Error_Description]' +
' ,[Error_Column_Name]' +
' ,[Error_Cleared]) '+
'select Ident,Clm_aud_nbr, Procedure_code, '+
'orig_procedure_code_modifier_1, ' +
'Charged_amount, Allowed_Amount, '+
'Paid_Amount, Audit_Amount, '+
'Coinsurance_Amount, Deductible_Amount, '+
'Copay_Amount,First_Date_of_service, '+
'MRM_Claim_Ident,Member_Number, ' +
'Member_First_Name, Member_Last_Name, ' +
'''Error_Type'' = 2,''Error_Sub_Type'' = 1,' +
'''Error_Description'' = ''' + @er_msg + ''',' +
'''Error_Column_Name'' = ''' + @mrm_fld_name + ''',' +
'''Error_Cleared'' = 0 ' +
'from ODAR '+
'where sent = 0 and client_sub_ident = ' +
convert(varchar,@client_sub_ident) +
CASE WHEN @VV <> '' AND @CONDITION <> '' THEN
'AND (' + @mrm_fld_name + 'NOT IN (' +
@VV + ') OR NOT ' + @CONDITION + ')' ELSE
CASE WHEN @VV <> '' THEN
' AND (' + @mrm_fld_name + ' NOT IN (' +
@VV + ')'
WHEN @CONDITION <> '' THEN
' AND NOT ' + @CONDITION ELSE ''
END
END
--exec(@sql_str)
exec @error_cnt = sp_executesql @SQL_STR
set @intRows = @intRows + @error_cnt
FETCH NEXT FROM fld_list_cursor INTO @mrm_fld_name,
@vv, @condition, @er_msg,@client_sub_ident,
@format
end
close fld_list_cursor
deallocate fld_list_cursor
January 5, 2010 at 9:13 am
I agree with everything that's been said in this forum . . .
1) Cursors are BAD, BAD, BAD. If you can come up with a strategy where you don't need them, I strongly advise you do so.
2) We still don't know what you're trying to accomplish. What are you trying to do with this? If you can give us an idea of your goal (and please put it simply), we can help you out.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply