SQL Function Issue.Getting Strange Result

Question

SQL Function Issue.Getting Strange Result

KMPSSS

Ten Centuries

Points: 1052
More actions
May 21, 2010 at 10:35 am

#386985

Hello friends
i dont why its happening.Below is my table
Create table workorder
(
WoID nvarchar(10),
Loc nvarchar(5),
a nvarchar(10),
b nvarchar(10)
)
Insert into workorder
Select '0000000001','atp','a1','b1'
union all
Select '0000000002','atp','a2','b2'
union all
Select '0000000003','atp','a3','b3'
union all
Select '0000000004','atp','a4','b4'
Its funtion
CREATE FUNCTION getworkorder(
@WOID as NVARCHAR(10),
@LocID as NVARCHAR(5)
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM WorkOrder WHERE WoID = @WOID AND Loc = @LocID
)
GO
Here when id run the following query
Select * from dbo.getworkorder('0000000004','atp')
output
0000000004 atp a4 b4
here im getting four field result.Its fine.
If i add new column in my table
alter table workorder add c nvarchar(10)
Go
insert into Workorder values('0000000005','atp','a5','b5','c5')
Go
Again run the same funtion like below
Select * from dbo.getworkorder('0000000005','atp')
output
1 0000000005 atp a5 b5
Here missing fifth column value . what can i do for this. If alter the funtion and run the same query im getting fifth column result also.
How can i achieve this result without altering funtion. need to reterive all columns, if i add any new fileds

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is normal; the execution plan is cached;

when you create a proc/function/view, and it features SELECT * FROM, behind the scenes the actual query is stored as SELECT Col1,Col2,Col3.... for performance reasons.

unless you recompile the proc function, it will remain with the defined columns the day you built it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Didn't anyone told you that "SELECT * FROM TABLE" is a bad habit 🙂

I afraid you will not be able to achieve what you want with UDF.

I can suggest to create your function WITH SCHEMABINDING option instead. It will prevent you to change the table which is referred in the function without droping the function first 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 3

You could also explore DDL Triggers to update the function when the table was updated. In general though, don't use SELECT *.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]