Stored Procedure - exec failed ?!

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 2005 Forums

Transact-SQL (2005)

Stored Procedure - exec failed ?!

Author

Topic

nille
Starting Member

3 Posts

Posted - 2009-08-10 : 03:51:33

Hello,

can somebody help me with the following stored procedure syntax?


USE [Coram]
GO
/****** Object:  StoredProcedure [dbo].[TEST]    Script Date: 08/07/2009 12:18:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TEST]
	@ArtNr		int,
	@ArtOmschr  nvarchar(25),
	@Taal		nvarchar(1)
AS
BEGIN

SELECT 
			  case when [Art].[Status]=0 then str([Art].[Nummer]) else str([Art].[Nummer]) + '*' end as nr
			  ,case when @Taal='F' then [Omschr_fr] else [Omschr_nl] end as omschr 
			  ,[Art].[EAN]
			  ,[Art].[Winkelprijs]
			  ,[Art].[Signal]
			FROM [Coram].[dbo].[Artikels] as [Art]
			WHERE ([Art].[Nummer] <> 0)

			IF ((CAST(@ArtNr as nvarchar(15)) <> '') and (@ArtOmschr <> ''))
			BEGIN
				AND (CAST([Art].[Nummer] as nvarchar(15)) LIKE (CAST(@ArtNr as nvarchar(15)) + '%'))

				AND (
						([Art].[Omschr_nl] LIKE (@ArtOmschr + '%'))
					 OR ([Art].[Omschr_fr] LIKE (@ArtOmschr + '%'))
					)
			END
			ORDER BY [Art].[Nummer]	

END

The keyword near AND is wrong they say, but I don't know where or how can I solve this..?

Can somebody help me please?

Error-snapshot:
[url]http://www.helpmij.nl/forum/attachment.php?attachmentid=68367&d=1249649106[/url]

Sachin.Nand

2937 Posts

Posted - 2009-08-10 : 03:59:59

Use case statement.

PBUH

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-10 : 04:00:44

[code]SELECT
case when [Art].[Status]=0 then str([Art].[Nummer]) else str([Art].[Nummer]) + '*' end as nr
,case when @Taal='F' then [Omschr_fr] else [Omschr_nl] end as omschr
,[Art].[EAN]
,[Art].[Winkelprijs]
,[Art].[Signal]
FROM [Coram].[dbo].[Artikels] as [Art]
WHERE ([Art].[Nummer] <> 0)
AND (CAST([Art].[Nummer] as nvarchar(15)) LIKE (CAST(@ArtNr as nvarchar(15)) + '%'))
AND (
([Art].[Omschr_nl] LIKE (@ArtOmschr + '%'))
OR ([Art].[Omschr_fr] LIKE (@ArtOmschr + '%'))
)
AND ((CAST(@ArtNr as nvarchar(15)) <> '') and (@ArtOmschr <> ''))
ORDER BY [Art].[Nummer] [/code]
like this?

Hope can help...but advise to wait pros with confirmation...

nille
Starting Member

3 Posts

Posted - 2009-08-10 : 05:08:52

Ok, I know that;

but sorry, what I forgot to tell is that I call this stored procedure from more then one place,
and then sometimes the @ArtNr is '' then the where must be totaly different,

so what I wan't to tell you guys is:
It's a query, but the WHERE statement need to be different when my @ArtNr and/or @ArtOmschr is '' or not ''.
if the parameters exist they need to be in the where statement , in the other one not!

I hope it's not to complicated my sentence?

nille
Starting Member

3 Posts

Posted - 2009-08-10 : 11:57:25

Ok, thx but now I want 3 diffrent ways to make a decission;
And this does'nt work, anybody an idea?



SELECT
          case when [Art].[Status]=0 then str([Art].[Nummer]) else str([Art].[Nummer]) + '*' end as nr
          ,case when @Taal='F' then [Omschr_fr] else [Omschr_nl] end as omschr      
          ,isnull([OR].[Qty],0) as Qty      
          ,isnull([Omzet]  .[OmzetAantalVorigJaar],0) as Omzet#Vj   
          ,isnull([Omzet]  .[OmzetAantalDitJaar],0)  as Omzet#Dj
          ,case [Klant].[Tarief]
            when '0' then [Art].[Prijs1]
            when '1' then [Art].[Prijs2]
            when '2' then [Art].[Prijs3]
            else [Art].[Prijs4]
           end as Prijs
          ,[Signal]
        FROM [Coram].[dbo].[Artikels] as [Art]  
            LEFT JOIN [Coram].[dbo].[Omzet] ON ([Omzet].[Klantnr]  = @KlantNr
                and [Art].[Nummer]=[Omzet].[Artikelnr])
            LEFT JOIN [Coram].[dbo].[BestelOR] as [OR] ON ([Art].[Nummer]=[OR].[Artikelnr]  
                and [OR].[Type] = @Type
                and [OR].[Vtwn]  = @Vtwn
                and [OR].[Bestnr]  = @Bestnr
                and [OR].[Klantnr]  = @KlantNr)
            LEFT JOIN [Coram].[dbo].[Klanten] as [Klant] ON ([Klant].[Nummer] = @KlantNr)
            WHERE ([Art].[Nummer] <> 0)
            
            case when (CAST(@ArtNr as nvarchar(15)) <> '0') then
                    case when (@ArtOmschr <> '#') then
                        AND (CAST([Art].[Nummer] as nvarchar(15)) LIKE (CAST(@ArtNr as nvarchar(15)) + '%'))
                        AND (
                                ([Art].[Omschr_nl] LIKE (@ArtOmschr + '%'))
                             OR ([Art].[Omschr_fr] LIKE (@ArtOmschr + '%'))
                            )
                    else
                        AND (CAST([Art].[Nummer] as nvarchar(15)) LIKE (CAST(@ArtNr as nvarchar(15)) + '%'))
                    end
            else
                AND (
                        ([Art].[Omschr_nl] LIKE (@ArtOmschr + '%'))
                     OR ([Art].[Omschr_fr] LIKE (@ArtOmschr + '%'))
                    )
            end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources