October 29, 2012 at 10:15 am
Hi..I am trying to create Sp as below:
CREATE PROCEDURE [dbo].[usp_Get_Investor]
@InvestorCode_FROM INT
,@InvestorCode_TO INT
,@Investor_all VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON
IF(@InvestorCode_FROM AND @InvestorCode_TO IS NOT NULL)
BEGIN
SELECT DISTINCT(OWN.INVESTOR)
FROM dbo.TABLEA OWN
WHERE OWN.INVESTOR >= @InvestorCode_FROM
AND OWN.INVESTOR <= @InvestorCode_TO
END
ELSE IF (@InvestorCode_FROM OR @InvestorCode_TO IS NULL) AND (@Investor_all IS NOT NULL)
BEGIN
SELECT DISTINCT(OWN.INVESTOR)
FROM dbo.TABLEA OWN
WHERE OWN.INVESTOR IN (SELECT * From dbo.UFN_SPLIT(@Investor_all ,','))
END
END
ERROR:
Msg 4145, Level 15, State 1, Procedure usp_Get_Investor, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
Msg 156, Level 15, State 1, Procedure usp_Get_Investor, Line 35
Incorrect syntax near the keyword 'ELSE'.
Msg 4145, Level 15, State 1, Procedure usp_Get_Investor, Line 35
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
Can anyone tell me whats the error? And how to correct it?
Thanks,
Komal
October 29, 2012 at 10:25 am
komal145 (10/29/2012)
Hi..I am trying to create Sp as below:CREATE PROCEDURE [dbo].[usp_Get_Investor]
@InvestorCode_FROM INT
,@InvestorCode_TO INT
,@Investor_all VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON
IF(@InvestorCode_FROM AND @InvestorCode_TO IS NOT NULL)
BEGIN
SELECT DISTINCT(OWN.INVESTOR)
FROM dbo.TABLEA OWN
WHERE OWN.INVESTOR >= @InvestorCode_FROM
AND OWN.INVESTOR <= @InvestorCode_TO
END
ELSE IF (@InvestorCode_FROM OR @InvestorCode_TO IS NULL) AND (@Investor_all IS NOT NULL)
BEGIN
SELECT DISTINCT(OWN.INVESTOR)
FROM dbo.TABLEA OWN
WHERE OWN.INVESTOR IN (SELECT * From dbo.UFN_SPLIT(@Investor_all ,','))
END
END
ERROR:
Msg 4145, Level 15, State 1, Procedure usp_Get_Investor, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
Msg 156, Level 15, State 1, Procedure usp_Get_Investor, Line 35
Incorrect syntax near the keyword 'ELSE'.
Msg 4145, Level 15, State 1, Procedure usp_Get_Investor, Line 35
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
Can anyone tell me whats the error? And how to correct it?
Thanks,
Komal
You have an incomplete condition here and you are using it twice.
ELSE IF (@InvestorCode_FROM OR @InvestorCode_TO IS NULL) AND (@Investor_all IS NOT NULL)
I think you want something like this:
IF (@InvestorCode_FROM IS NULL OR @InvestorCode_TO IS NULL) AND (@Investor_all IS NOT NULL)
I would also recommend that you look at the link in my signature about splitting strings. I am guessing that by the way you are using your function that is a loop based nibbler splitter. They work but are horribly slow.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2012 at 10:33 am
Oh yaa.Realized the mistake.... I corrected it.It worked.Thanks.
But when I tried to run the Sp by passing @InvestorCode_FROM and @InvestorCode_TO and not giving @Investor_all.
Example
EXEC [dbo].[usp_Get_Investor] 1000 ,2000
It throws error :' Procedure or function 'usp_Get_Investor' expects parameter '@Investor_all', which was not supplied.'
October 29, 2012 at 10:41 am
komal145 (10/29/2012)
Oh yaa.Realized the mistake.... I corrected it.It worked.Thanks.But when I tried to run the Sp by passing @InvestorCode_FROM and @InvestorCode_TO and not giving @Investor_all.
Example
EXEC [dbo].[usp_Get_Investor] 1000 ,2000
It throws error :' Procedure or function 'usp_Get_Investor' expects parameter '@Investor_all', which was not supplied.'
That is because those are not optional parameters. You need to provide a default value if you want to not pass certain parameters.
CREATE PROCEDURE [dbo].[usp_Get_Investor]
@InvestorCode_FROM INT = null
,@InvestorCode_TO INT = null
,@Investor_all VARCHAR(MAX) = null
You should get into the habit of ALWAYS naming your parameters for procedure and function calls.
EXEC [dbo].[usp_Get_Investor] @InvestorCode_FROM = 1000 , @InvestorCode_TO = 2000
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2012 at 11:02 am
Thanks.GOt it.
So, I tried Executing your way.
EXEC [dbo].[usp_Get_Investor] @InvestorCode_FROM=10100 ,@InvestorCode_TO=12600
But there are few values. But returns nothing.
Just displays: Command(s) completed successfully. 🙁
October 29, 2012 at 11:05 am
Ignore....above message it found out the error !!! I added something in the code and saved it ..and forgot to remove.My bad.
Anyways thank you for your help.
Solved the issue.
July 29, 2017 at 5:25 am
"and (case when c.CustomerSAP='167791' then '668194' else c.CustomerSAP end)
Its no working. I had error:
"An expression of non-boolean type specified in a context"
I'll try that:
and (case when c.CustomerSAP='167791' then '668194' end)=668194
But in table didn't change me No 167791 on 668194.
What can I do? I'll be gratefull for any idea.
and (case when c.CustomerSAP='167791' then '668194' end)=668194
July 31, 2017 at 7:10 am
kingezja - Saturday, July 29, 2017 5:25 AMHi,
I Have very similar problem.When I make case."and (case when c.CustomerSAP='167791' then '668194' else c.CustomerSAP end)
Its no working. I had error:
"An expression of non-boolean type specified in a context"
I'll try that:
and (case when c.CustomerSAP='167791' then '668194' end)=668194
But in table didn't change me No 167791 on 668194.What can I do? I'll be gratefull for any idea.
and (case when c.CustomerSAP='167791' then '668194' end)=668194
For starters this thread is 5 years old. Second your question doesn't make a lot of sense here because we have no context at all. Why do you think a case expression would change the value in your table? Is this part of an update? Start a new thread with your question but before you do please see the first link in my signature for best practices when posting questions. Many people will be happy to help you if you provide us details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply