how to make a condition statement when Count is =

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

General SQL Server Forums

New to SQL Server Programming

how to make a condition statement when Count is =

Author

Topic

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-12 : 23:09:49

Hi,

I just a little bit confused in making a stored procedure,

i have a stored procedure whereas i will generate a Id number using count so the problem is i want to start the number of record into 1 but it always start from 0 since the id depends on the number of record so if the UI saw that there is no record yet saved in the database it will get the id "0" please see below code for my table and stored pro.

CREATE TABLE [dbo].[countNumber](
[CPAR-NONCOM] [int] NULL,
[CPAR-OBS] [int] NULL,
[CPAR-DISP] [int] NULL,
[CPAR-IAR] [int] NULL,
[YEAR] [nchar](10) NULL
) ON [PRIMARY]

GO

stored pro:

ALTER procedure [dbo].[usp_GetNCNumber]
(
@type as nvarchar(50)
)
AS
BEGIN
DECLARE @num as nvarchar(13)
DECLARE @curnum as nvarchar(13)
DECLARE @prefix as nvarchar(13)

if @type = 'Dispen'
begin
SET @prefix = 'DIS'
SET @num = (select COUNT(cor) from dbo.ForSample where cor = @type and IsApprove is not null)
end

SET @num = (SELECT dbo.PadString (@num, '0', 3))
SET @curnum = @prefix + CAST (Year(getdate()) as nvarchar(10)) + @num

UPDATE dbo.countNumber
SET [CPAR-NONCOM] = [CPAR-NONCOM] + 1

select @curnum [CPAR-NONCOM]

END

i tried to make something like this but no luck:
error "Incorrect syntax near the keyword 'IF'

IF (select COUNT(cor) FROM dbo.ForTesting) = 0 SET @num = 1 ELSE SELECT @num = COUNT(cor) from dbo.ForTesting where cor = @type and IsApprove is not null)

please advise.
thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-12 : 23:38:46

Just add an identity column to your dbo.ForSample table and seed it with 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 00:00:32

you cant do IF like this. it should be case when

SELECT @num = CASE WHEN (select COUNT(cor) FROM dbo.ForTesting) = 0 THEN 1 ELSE COUNT(cor) from dbo.ForTesting where cor = @type and IsApprove is not null)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 00:57:53

Hi Visakh,

i have manage to use this but when i run it yes it the ID will return a value of 0 but if i run it again it will return a value again of 1 it should be number 2 so i think the problem is because it is getting the id base on record count so how can i do this that it will always start at 1 and not in 0 here is my current code:

IF @type = 'Dispensation'
BEGIN
SET @prefix = 'DIS'
Set @num = (SELECT CASE WHEN count(CorPreAction) = 0 THEN 1 ELSE count(CorPreAction)
END
FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)
END

Please advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 01:00:08

whats it that you're trying to generate in @num?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 01:11:28

I wanted to generate a ID number cause id number will depend on what type of report user chosen there are 3 types DIS, OBS and IAR and it will only save in one table. and i wanted that each type of report will have a unique type of id which compose of prefix + year + padstring + @num which is the number.

so what i really want is to start from 1 cause in my query it always start from 0 since it is base on record count.

hope you understand me visakh what i only want is to start from 1 and not 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 01:18:28

make it like:-

 
...
IF @type = 'Dispensation'
BEGIN 
SET @prefix = 'DIS'
Select @num = COALESCE(NULLIF(Cnt,0),1)
FROM(SELECT count(CorPreAction) AS Cnt FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)t
END
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 01:29:08

Hi,
I try the code you gave but still not solve I input 3 report with the type of DIS here is the result..

DIS2011001
DIS2011001
DIS2011002

any advise?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 02:15:44

[code]
IF @type = 'Dispensation'
BEGIN
SET @prefix = 'DIS'
Select @num = COALESCE(Cnt,0)+1
FROM(SELECT count(CorPreAction) AS Cnt FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)t
END
...

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 03:38:12

Hi Visakh,

Just want to know if how can i convert to that code if i will be having this code here is my current code:

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = (select CASE WHEN COUNT(CorPreAction) = 0 Then 1 ELSE count(CorPreAction)
END
from dbo.ForTesting where CorPreAction like '%Non-conformity' and IsApproveByQMR is not null)
End

and i wanted to use the code you have send to me cause i have two type of report where i will use only the same @num

here is the other:

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = (select CASE WHEN COUNT(CorPreAction) = 0 Then 1 ELSE count(CorPreAction)
END
from dbo.ForTesting where CorPreAction like '%Non-conformity' and IsApproveByQMR is not null)
End

the return value should be if type is major it will return 1 and if minor will return 2 if send again return 3 etc. its just like using one type of ID on Minor and Major. hope you understand my query

Please advise.
thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 04:28:54

you mean major and minor should get continuos numbers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 04:33:46

Yes for example,

if 1st report is minor will return 1 if 2nd is major will return 2 if third is major return 3 if fourth is minor return 4 and so on so both minor and major are using continuos numbers. :) here is my current code.

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)
END receive an error

please advise buddy.
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 04:38:21

then isnt this enough? no need of if and also filter based on CorPreAction


..
 begin
SET @prefix = CASE WHEN @type = 'Major Non-conformity'
 THEN 'NCR'
WHEN @type = 'Minor Non-conformity'
 THEN 'NCR'
END
SET @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE IsApproveByQMR IS NOT NULL)t
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 04:47:43

Hi I receive an error,

Msg 156, Level 15, State 1, Procedure usp_GetNCNumber, Line 27
Incorrect syntax near the keyword 'FROM'.

Msg 156, Level 15, State 1, Procedure usp_GetNCNumber, Line 27
Incorrect syntax near the keyword 'From'.

here is my whole code:

USE [TESTING]
GO
/****** Object: StoredProcedure [dbo].[usp_GetNCNumber] Script Date: 09/13/2011 11:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_GetNCNumber]
(
@type as nvarchar(50)
)
AS
BEGIN
DECLARE @num as nvarchar(13)
DECLARE @curnum as nvarchar(13)
DECLARE @prefix as nvarchar(13)

IF @type = 'Dispensation'
BEGIN
SET @prefix = 'DIS'
Select @num = COALESCE(Cnt,0)+1
FROM(SELECT count(CorPreAction) AS Cnt FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SET @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)
END

IF @type = 'Observation'
BEGIN
SET @prefix = 'OBS'
Select @num = COALESCE(Cnt,0)+1
FROM(SELECT count(CorPreAction) AS Cnt FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)t
END

IF @type = 'Improvement action request'
BEGIN
SET @prefix = 'IAR'
Select @num = COALESCE(Cnt,0)+1
FROM(SELECT count(CorPreAction) AS Cnt FROM dbo.ForTesting WHERE CorPreAction = @type AND IsApproveByQMR IS NOT NULL)t
END

SET @num = (SELECT dbo.PadString (@num, '0', 3))
SET @curnum = @prefix + CAST (Year(getdate()) as nvarchar(10)) + @num

UPDATE dbo.numreference
SET [CPAR-NONCOM] = [CPAR-NONCOM] + 1

select @curnum [CPAR-NONCOM]

END

the only problem is the minor and the major. please advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 04:56:02

why do you need ifs again? as i understand you want number to be continuos for all types so you need just one statement for getting @num.
also it should SELECT @num rather than SET which is why the error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 05:02:14

No,

the continuos number is for the major and non conformity, and for the other type it is separate numbers so meaning they will all have a separate number like for DIS OBS IAR and NCR so there is no problem with the three the only problem is on the NCR cause it will be using the same type (NCR). I wanted for both Minor and Major to use the one @type or to combine those NCR's

Hope you understand my query buddy.
please advise
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 06:17:11

ok thenits fine. you just need to change SET to SELECT in statements where you get value for @num

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 12:00:20

Hi Visakh,
so it will be like this:

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SELECT@num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SELECT @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)
END

am i right? if not feel free to edit it,,

please advise. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 12:08:03

quote:
Originally posted by iamnewtosql

Hi Visakh,
so it will be like this:

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SELECT@num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SELECT @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

am i right? if not feel free to edit it,,

please advise. Thanks!

small modification

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 21:02:18

what do you mean by small modification????
sorry cause im still new here.. hope you understand

upon using my code below i receive an error,

if @type = 'Major Non-conformity'
begin
SET @prefix = 'NCR'
SELECT @num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

if @type = 'Minor Non-conformity'
begin
SET @prefix = 'NCR'
SELECT@num = COALESCE (Cnt,0)+1
FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t
END

here is the error:

Msg 156, Level 15, State 1, Procedure usp_GetNCNumber, Line 25
Incorrect syntax near the keyword 'From'.
Msg 102, Level 15, State 1, Procedure usp_GetNCNumber, Line 33
Incorrect syntax near 'SELECT@num'.
Msg 156, Level 15, State 1, Procedure usp_GetNCNumber, Line 34
Incorrect syntax near the keyword 'From'.

please advise buddy.

iamnewtosql
Starting Member

11 Posts

Posted - 2011-09-13 : 22:36:37

Hi,

i think i found the problem,
change this FROM (SELECT count (CorPreAction AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t

to

FROM (SELECT count (CorPreAction) AS Cnt From dbo.ForTesting WHERE CorPreAction like '%Non-conformity' AND IsApproveByQMR IS NOT NULL)t

the error occurs because of the first one doesnt have a close parenthesis for CorPreAction,, lol anyway thank you very much visakh hope to see you in my next thread about sql,,,

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources