June 26, 2008 at 3:16 am
hello,
Can any one help out whdi this sp plz
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Find.NunFamiliares]
(
@Nome NVARCHAR(225),
@ID_Tipo NVARCHAR(2),
@ID_Categoria NVARCHAR (2),
@Telefone NVARCHAR(20)
)
AS
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT
p.Nome,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (e.ID_Pessoa) FROM [Empregado]AS e WHERE e.ID_Pessoa = '+@ID_Tipo+') > 0
THEN ''+Empregado+''
ELSE ''+Patrão+''
END,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (p.ID_Pessoa) FROM [Patrao] AS pa WHERE pa.ID_Pessoa = '+@ID_Categoria+')
THEN (SELECT Designacao FROM [Departamento])
ELSE (SELECT Designacao FROM [Cargo] WHERE ID_Pessoa = '+@ID_Categoria+')
END,
p.Telefone,
(SELECT count(*) FROM [Familiares] AS f)
FROM [Pessoa] AS p
LEFT JOIN [Familiares]AS f
ON p.ID_Pessoa = f.ID_Pessoa
GROUP BY Nome,ID_Pessoa,Telefone'
IF @Nome ='' AND @ID_Tipo = '' AND @ID_Categoria = '' AND @Telefone = ''
BEGIN
RETURN
END
ELSE
BEGIN
IF @Nome<>''BEGIN
SET @SQL = @SQL + 'AND Nome LIKE ''%'+@Nome+'%'''
END
IF @Telefone <> '' BEGIN
SET @SQL = @SQL + 'AND Replace (Telefone,'' '','''') LIKE ''%'' REPLACE ('''+@Telefone+''','' '','''') +''%'''
END
EXEC(@SQL)
END
thanks 😉
June 26, 2008 at 3:19 am
What sort of help do you need with it?
June 26, 2008 at 3:40 am
hello 🙂
when i exec the sp its gives thsi error
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'FROM'.
June 26, 2008 at 4:13 am
Hi r.medeiros,
Check the Dynamic Sql ..
Where the Syntax error its showing...
SET @SQL = 'SELECT
p.Nome,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (e.ID_Pessoa) FROM [Empregado]AS e WHERE e.ID_Pessoa = '+@ID_Tipo+') > 0
THEN ''+Empregado+''
ELSE ''+Patrão+''
END,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (p.ID_Pessoa) FROM [Patrao] AS pa WHERE pa.ID_Pessoa = '+@ID_Categoria+')
THEN (SELECT Designacao FROM [Departamento])
ELSE (SELECT Designacao FROM [Cargo] WHERE ID_Pessoa = '+@ID_Categoria+')
END,
p.Telefone,
(SELECT count(*) FROM [Familiares] AS f)
FROM [Pessoa] AS p
LEFT JOIN [Familiares]AS f
ON p.ID_Pessoa = f.ID_Pessoa
GROUP BY Nome,ID_Pessoa,Telefone'
Check with --Select @SQL
Cheers!
Sandy.
--
June 26, 2008 at 8:39 am
This SP, builds up the SQL statement in the @sql clause and then EXEC (@sql) executes it in the ending notes.
Use PRINT (@SQL) insted of EXEC (@SQL)
also use PRINT Statements to get the format of the Query at varous STEPS.
ALTER PROCEDURE [dbo].[Find.NunFamiliares]
(
@Nome NVARCHAR(225),
@ID_Tipo NVARCHAR(2),
@ID_Categoria NVARCHAR (2),
@Telefone NVARCHAR(20)
)
AS
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT
p.Nome,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (e.ID_Pessoa) FROM [Empregado]AS e WHERE e.ID_Pessoa = '+@ID_Tipo+') > 0
THEN ''+Empregado+''
ELSE ''+Patrão+''
END,
CASE p.ID_Pessoa
WHEN (SELECT COUNT (p.ID_Pessoa) FROM [Patrao] AS pa WHERE pa.ID_Pessoa = '+@ID_Categoria+')
THEN (SELECT Designacao FROM [Departamento])
ELSE (SELECT Designacao FROM [Cargo] WHERE ID_Pessoa = '+@ID_Categoria+')
END,
p.Telefone,
(SELECT count(*) FROM [Familiares] AS f)
FROM [Pessoa] AS p
LEFT JOIN [Familiares]AS f
ON p.ID_Pessoa = f.ID_Pessoa
GROUP BY Nome,ID_Pessoa,Telefone'
PRINT (@SQL)
IF @Nome ='' AND @ID_Tipo = '' AND @ID_Categoria = '' AND @Telefone = ''
BEGIN
RETURN
PRINT (@SQL)
END
ELSE
BEGIN
IF @Nome<>''BEGIN
SET @SQL = @SQL + 'AND Nome LIKE ''%'+@Nome+'%'''
PRINT (@SQL)
END
IF @Telefone <> '' BEGIN
SET @SQL = @SQL + 'AND Replace (Telefone,'' '','''') LIKE ''%'' REPLACE ('''+@Telefone+''','' '','''') +''%'''
PRINT (@SQL)
END
PRINT (@SQL)
END
Maninder
www.dbanation.com
June 26, 2008 at 2:27 pm
The Case statements in the SQL string aren't written correctly.
Change "CASE p.ID_Pessoa" to just "CASE". If you have a variable or column name after Case, you can't do complex comparisons in the When statements, just equality statements.
For example:
Case Age
when 5 then 'Five'
when 6 then 'Six'
End
That one is okay.
Case Age
when Age <= 5 then 'Five or less'
when 6 then 'Six'
End
That one isn't okay, because a complex condition follows a When.
Case
when Age <= 5 then 'Five or less'
when Age = 6 then 'Six'
End
That one is okay, because no column or variable name follows Case. That means it can have complex comparisons after When.
Try fixing both Case statements, and see if that gets it going.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 26, 2008 at 4:12 pm
Hello,
Thanks for the replay and for the help 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply