CODEWITHSUNDEEP
sqlsql-server
Gaurav Agrawal
Gaurav Agrawal

Reputation: 4431

Getting different values from view

I have a two tables and a view for them. Having inline query

select * from View_tbl where sector = '04'

but when i creates stored procedure for this

create proc spTest
@sector varchar(2)
as
select * from View_tbl where sector = @sector

both returns dataset with different values. SP Returns those columns too which are in Main tbl but not in view.

Any suggestions please

Upvotes: 0

Views: 86

Answers (2)

njr101
njr101

Reputation: 9619

If the definition of your view has changed, you may need to recompile the stored procedure.

When you use select * in a stored procedure, this will get compiled down to an explicit list of columns. Subsequent changes to the view definition may not be reflected in the definition of the sp (depending on a few other factors and the version of SQL Server).

In general, I try to avoid select * in stored procedures and code the list of columns explicitly. This avoids the dependency on recompilation.

create proc spTest
    @sector varchar(2)
as
    select
        col1,
        col2,
        col3
from
    View_tbl
where
    sector = @sector

Upvotes: 1

Mark Monforti
Mark Monforti

Reputation: 473

My guess is that you have the SP in two places by accident (check Master) and it executing the SP in the wrong database maybe an old version of your sp is laying around...Maybe fully qualify your tables with Database as sort term test.

Upvotes: 0

Related Questions