CONDITIONAL IF SATEMENT USING TEMP TABLES
I tried doing this as a function but no one is able to help me right away (see CONDITIONAL IF SATEMENT IN A SQL FUNCTION question)
Now I am trying to filter the results usinf temp tables and I still get an error.
CAN YOU DO IT?
--declare @EmpID int
--set @EmpID = '4'
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia > '1')
END
Now I am trying to filter the results usinf temp tables and I still get an error.
CAN YOU DO IT?
--declare @EmpID int
--set @EmpID = '4'
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia > '1')
END
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
now, what is/was the error?
You cannot use SELECT ... INTO in a function. You have to use a stored procedure for this purpose.
Rereading the q, I have to agree with rafrancisco,
select into is not usable inside a function.
Can you explain why you want a function and not a stored procedure?
select into is not usable inside a function.
Can you explain why you want a function and not a stored procedure?
ASKER
The funtion allows me to pass parameters and iclude in another SQL scripts in place of a table, storeprocedures cannot be incorporated in the SQL query I am working with, see question "CONDITIONAL IF SATEMENT IN A SQL FUNCTION" I posted earlier.
I am reviewing Comment from angelIII to see if it works for what I need.
I am reviewing Comment from angelIII to see if it works for what I need.
can you post more details about the query and the function you are trying to implement, as said above, my comment above will not work (i didn't read the q properly)
see the other question ,
has my answer solved it?
has my answer solved it?
ASKER
Comment from angelIII fails when I change the Empid value in the query
ASKER
Function issue was solved by Comment from Lowfatspread
Can it be done using tables as seen in this thread
Can it be done using tables as seen in this thread
You could use a function that returned a table, like so:
CREATE FUNCTION GetOrdersInfo (
@EmpID INT
)
RETURNS TABLE
AS
RETURN (
SELECT EmployeeID, ShipCity
FROM Orders
WHERE (@EmpID = 4 AND ShipVia = '1')
OR (@EmpID = 6 AND ShipVia > '1')
)
CREATE FUNCTION GetOrdersInfo (
@EmpID INT
)
RETURNS TABLE
AS
RETURN (
SELECT EmployeeID, ShipCity
FROM Orders
WHERE (@EmpID = 4 AND ShipVia = '1')
OR (@EmpID = 6 AND ShipVia > '1')
)
So you don't need an IF, just a change to the WHERE clause.
Actually, I forgot to take away the IF statement, so the select is what scottpletcher posted already:
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE ( @EmpID = '4' and ShipVia = '1')
OR ( @EmpID = '6' and ShipVia > '1')
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE ( @EmpID = '4' and ShipVia = '1')
OR ( @EmpID = '6' and ShipVia > '1')
Yes, sorry angelIII, I was in a hurry and just looked at the original q and last comment.
ASKER
I just got a chance to get back to this...
Comment from angelIII above gives no results, it also doesn't follow the scope of my original question, I need to pass variables to this storeproc, and execute the script according to the value of @EmpID using IF statements.
Comment from angelIII above gives no results, it also doesn't follow the scope of my original question, I need to pass variables to this storeproc, and execute the script according to the value of @EmpID using IF statements.
create procedure do_some_work
@EmpID int
AS
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia > '1')
END
GO
and it can be called like this:
exec do_some_work 4
exec do_some_work 6
@EmpID int
AS
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE (ShipVia > '1')
END
GO
and it can be called like this:
exec do_some_work 4
exec do_some_work 6
ASKER
That is Identical to my original query, it fails. I wnat a modification to it that will parse successfully.
Take my original query and run it in Query Analizer, then modify it to run without errors.
I am using the Northwind database so we can test it.
VZ
Take my original query and run it in Query Analizer, then modify it to run without errors.
I am using the Northwind database so we can test it.
VZ
ASKER
Thank you for all your help, I was able to solve the issue on my own...
declare @EmpID int
set @EmpID = '6'
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP_1
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP_2
FROM Orders
WHERE (ShipVia > '1')
END
IF (@EmpID= '4')
begin
Select *
from #TMP_SHIP_1
drop table #TMP_SHIP_1
END
IF (@EmpID= '6')
begin
Select *
from #TMP_SHIP_2
drop table #TMP_SHIP_2
END
declare @EmpID int
set @EmpID = '6'
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP_1
FROM Orders
WHERE (ShipVia = '1')
END
IF (@EmpID= '6')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP_2
FROM Orders
WHERE (ShipVia > '1')
END
IF (@EmpID= '4')
begin
Select *
from #TMP_SHIP_1
drop table #TMP_SHIP_1
END
IF (@EmpID= '6')
begin
Select *
from #TMP_SHIP_2
drop table #TMP_SHIP_2
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The criteria in each of my select statements will change depending on the parameter, each IF statement will have different colums and stored into the temp table to be pulled from a storeds procedure, all I really wanted was the ability to use temp tables in combination with IF statements, I couldn't use the same temp table name so I just used different names, the outcome returns a large table with different criteria, your solution would be too combersome when using multiple colums. Thanks for uyour help.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: angelIII
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
Imran_Fast
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: angelIII
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
Imran_Fast
EE Cleanup Volunteer
ASKER
What happens when noe of the experts answers my question? I didn't see an option to abort. AngelIII though he tried to help did not provide the correct solution, I do not mind giving him the acceptance but I would not reocmment his comments as the solutin for this question.
VicZ
VicZ
IF (@EmpID= '4')
begin
SELECT EmployeeID, ShipCity
INTO #TMP_SHIP
FROM Orders
WHERE ( @EmpID = '4' and ShipVia = '1')
OR ( @EmpID = '6' and ShipVia > '1')
END