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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-04 : 20:17:10
|
sergio writes "I am creating a sp where I need a date and a key word that it will be compared with a field text to pass inside of the sp. I am creating a variable inside of the sp that is going connecting the whole command select and in the end I execute is variable, more or less like this:
exec sp_pesqserv "01/03/2001", "30/03/2001", ["test"]
and in the sp:
how to do connected in a string:
select @ vsql = @ vsql + ' dtcasatro between " '+ @ periodo1+' " and "'+@periodo2+' "'
and
select @ vsql = @ vsql + ' dscdef like "% '+@chave+'% "'
below it follows every procedure:
Thank you
CREATE PROCEDURE [sp_pesqservico] (
@equip smallint,
@usu varchar(30),
@status smallint,
@natureza char(10),
@periodo1 datetime,
@periodo2 datetime,
@chave varchar(200)
)
AS
declare @vsql varchar(8000), @flag bit
select @flag = 0
select @vsql='SELECT * FROM tservico'
if @equip <> 0
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' codequip = @equip '
end
if @usu <> ''
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' usuario = @usu '
end
if @status <> 0
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' codstatus = @status '
end
if @natureza <> ''
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' natdef = @natureza '
end
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' dtcadastro BETWEEN [@periodo1] AND [@periodo2] '
if @chave <> ''
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql +' descdef LIKE "%[@CHAVE]%" or providenci LIKE "%[@chave]%"'
end
execute (@vsql)
exec sp_pesqsolic @equip=1, @usu='',@status=0,@natureza='',@periodo1='',@periodo2='',@chave=''
execute sp_pesqsolic 2,["FIORINI"]
CREATE PROCEDURE [sp_pesqsolic]
@equip smallint =NULL,
@usu varchar(30)=NULL,
@status smallint
AS
declare @flag bit, @vsql varchar(8000)
select @flag = 0
select @vsql='SELECT * FROM tservico'
if @equip > 0
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' AND '
end
select @vsql = @vsql + ' codequip = '+convert(char(2),@equip)
end
if @usu <> ''
begin
if @flag = 0
begin
select @vsql = @vsql + ' WHERE '
select @flag = 1
end
else
begin
select @vsql = @vsql + ' A |
|
|
|
|
|
|
|
store procedure with datetime e text