August 13, 2009 at 8:25 pm
Dear all,
I having a table "Employee", with field "EmpID" & "EmpName".
Currently there are 3 records:
EmpID: 1
EmpName: Ashly
EmpID: 2
EmpName: Bob
EmpID: 3
EmpName: Charles
Q1: How should I write a stored procedure (sp_GetName) to select EmpName = "Bob" OR EmpName = "Charles"?
Q2: After retrieve record EmpName = "Bob" OR EmpName = "Charles", I need to insert these records into another table (TableName: TempEmployee)
Urgently required help. 🙂
August 13, 2009 at 9:34 pm
A1)
create procedure Emp_name_proc
(
@emp_id int
)
as
begin
select EmpName from Employee/*with lock options*/
where EmpID = @emp_id
end
A2)
insert into TempEmployee
exec Emp_name_proc 2
And it’s not advisable to create the procedure with SP_
Because it’s also reflects/access to other DB
August 13, 2009 at 9:57 pm
arun.sas (8/13/2009)
A1)
create procedure Emp_name_proc(
@emp_id int
)
as
begin
select EmpName from Employee/*with lock options*/
where EmpID = @emp_id
end
A2)
insert into TempEmployeeexec Emp_name_proc 2
And it’s not advisable to create the procedure with SP_
Because it’s also reflects/access to other DB
If I just use "exec Emp_name_proc 2", it will return me just "Bob", I need the "Charles" result as well 🙂
August 13, 2009 at 10:25 pm
create procedure Emp_name_proc
(
@emp_id int
)
as
begin
1) output like
‘Bob’
‘Charles’
select EmpName from Employee/*with lock options*/
where EmpID = @emp_id
union
select EmpName from Employee/*with lock options*/
where EmpID = @emp_id+1
2) output like ‘Bob,Charles’
declare @concat nvarchar(1000)
select @concat= COALESCE(@concat+',' , '')+ EmpName from Employee/*with lock options*/
where EmpID in(@emp_id,@emp_id+1)
select @concat
end
August 13, 2009 at 11:13 pm
arun.sas (8/13/2009)
create procedure Emp_name_proc(
@emp_id int
)
as
begin
1) output like
‘Bob’
‘Charles’
select EmpName from Employee/*with lock options*/where EmpID = @emp_id
union
select EmpName from Employee/*with lock options*/
where EmpID = @emp_id+1
2) output like ‘Bob,Charles’
declare @concat nvarchar(1000)select @concat= COALESCE(@concat+',' , '')+ EmpName from Employee/*with lock options*/
where EmpID in(@emp_id,@emp_id+1)
select @concat
end
I think my question is misleading.
I wish to have flexibility on the WHERE clause. The example that I gave is "Bob" and "Charles".
But if I wan select "Ashly" and "Charles"?
August 14, 2009 at 12:41 am
create table test
(
EmpID int,
EmpName varchar(20)
)
insert into test
select 1,'Ashly'
union all
select 2,'Bob'
union all
select 3,'Charles'
CREATE procedure Emp_name_proc
(
@emp_id varchar(100)
)
as
begin
declare @abc varchar(100)
select @abc = @emp_id
select @abc = 'select ''' + replace (@ABC,',',''' union select ''')+''''
create table #temp (name1 int)
insert into #temp (name1)
exec (@ABC)
select EmpName from test
where EmpID in (select name1 from #temp)
end
exec Emp_name_proc '1,2'
EmpName
Ashly
Bob
exec Emp_name_proc '1,3'
EmpName
Ashly
Charles
exec Emp_name_proc '2,3'
EmpName
Bob
Charles
exec Emp_name_proc '1,2,3'
EmpName
Ashly
Bob
Charles
August 14, 2009 at 1:54 am
Thanks for your help and I manage to solve this case.
"arun.sas" you are such a genius man !!! 😀
Remark: But cannot view Estimated Execution Plan, got error "Invalid object name '#temp'."
August 14, 2009 at 3:40 pm
I think this code is much simpler.
ALTER procedure Emp_name_proc
(
@emp_id varchar(100)
)
as
begin
declare @sql varchar(100)
select @sql = 'SELECT EmpName FROM Test WHERE EmpID IN (' + @emp_id + ')'
Exec( @sql )
end
No need for a temporary table. No need to modify the input string (assuming that you can be sure the inputs are separated by commas).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2009 at 5:41 pm
create table test
(
EmpID int,
EmpName varchar(20)
);
create table TempEmployee
(
EmpID int,
EmpName varchar(20)
);
insert into test
select 1,'Ashly'
union all
select 2,'Bob'
union all
select 3,'Charles'
declare @xmlNames xml;
select @xmlNames = '
Bob
Charles';
insert dbo.TempEmployee( empID, empName)
select tbl.empID, tbl.empName
from dbo.test tbl
where tbl.empName in (
select
names.n.value('.', 'varchar(20)')
from @xmlNames.nodes('/name') names(n)
)
August 19, 2009 at 6:28 am
for all these dynamic SQL things, you might want to consider reading this article:
http://www.databasejournal.com/features/mssql/article.php/3830981/T-SQL-Best-Practices--Part-2.htm
for example, the code drew posted,
ALTER procedure Emp_name_proc
(
@emp_id varchar(100)
)
as
begin
declare @sql varchar(100)
select @sql = 'SELECT EmpName FROM Test WHERE EmpID IN (' + @emp_id + ')'
Exec( @sql )
end
instead of saying exec (@sql), which runs the risk of having injection. what you could do is rewrite it to be:
set @sql = 'select EmpName FROM Test WHERE EmpID IN (' + @empid + ')'
EXEC sp_executesql @sql, N'@emp_id varchar(100)',@emp_id=@emp_id
this way, you can't throw injection in (well, at least not as easily)
also, if you read through the article, you'll see that when you're dealing with multiple optional where clauses, this approach can give you a much better execution rate, since you don't have to worry about your indexes not being used due to where clauses that are not being used
August 19, 2009 at 1:18 pm
set @sql = 'select EmpName FROM Test WHERE EmpID IN (' + @empid + ')'
EXEC sp_executesql @sql, N'@emp_id varchar(100)',@emp_id=@emp_id
this way, you can't throw injection in (well, at least not as easily)
also, if you read through the article, you'll see that when you're dealing with multiple optional where clauses, this approach can give you a much better execution rate, since you don't have to worry about your indexes not being used due to where clauses that are not being used
kramaswamy,
I think you meant to have this code like the following. Otherwise with the code above there will not be an embedded parameter. It would just be concatenating whatever value was in @empid into the original string being created in @sql.:
set @sql = 'select EmpName FROM Test WHERE EmpID IN (@empid)'
EXEC sp_executesql @sql, N'@emp_id varchar(100)',@emp_id=@emp_id
August 23, 2009 at 8:45 pm
drew.allen (8/14/2009)
I think this code is much simpler.
ALTER procedure Emp_name_proc
(
@emp_id varchar(100)
)
as
begin
declare @sql varchar(100)
select @sql = 'SELECT EmpName FROM Test WHERE EmpID IN (' + @emp_id + ')'
Exec( @sql )
end
No need for a temporary table. No need to modify the input string (assuming that you can be sure the inputs are separated by commas).
Drew
How if I want to make the select statement under "DECLARE CURSOR_ABC"?
August 24, 2009 at 12:13 am
You don't need any dynamic sql for this as I already demonstrated. You just give your procedure 1 parameter; the xml string with the names that you are looking for. No sql injection issues, no complicated parsing, no limitations with regards to cursors (which you should try and avoid at all costs, by the way) and -what i did not hear in this thread yet- no issues with choosing and implementing an escape sequence (i.e. what to do if one of the values in the comma separated string contains a comma). All that and it's a standard feature in SQL server 2005 and up.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply