Help
-
Hi All,
When I execute as below sqt text,I take this error message:
Server: Msg 156, Level 15, State 1, Procedure sel_genelsatifiyat2, Line 19
Incorrect syntax near the keyword 'Execute'.
Server: Msg 156, Level 15, State 1, Procedure sel_genelsatifiyat2, Line 28
Incorrect syntax near the keyword 'Execute'.
How can solve it?
Thanks all.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sel_genelsatifiyat2
(
@urunid int ,
@ozelkategori nvarchar(10),
@fiyatgrup nvarchar(30),
@miktar float,
@bfiyat float output,
@pbirim float output,
@tutar float output
 
AS
Declare @sql nvarchar(300)
Set @sql=' SELECT '+@fiyatgrup+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+@urunid+' AND FiyatKategorisi='''+@ozelkategori+''''
Set @bfiyat = Execute (@sql)
Set @sql=null
Set @sql=' SELECT '+@fiyatgrup+'Brm'+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+@urunid+' AND FiyatKategorisi='''+@ozelkategori+''''
Set @pbirim = Execute (@sql)
Set @tutar = @miktar*@bfiyat
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
What is EXEC(@sql) returning?
A resultset (one row with one column) or a return value?
N 56°04'39.16"
E 12°55'05.25" -
Set @sql=' SELECT '+@fiyatgrup+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+@urunid+' AND FiyatKategorisi='''+@ozelkategori+''''
Set @bfiyat = Execute (@sql) Returns one row with one column
-
CREATE TABLE #TEMP (i VARCHAR(200))
INSERT #Temp
EXEC(@sql)
select @bfiyat = i from #temp
N 56°04'39.16"
E 12°55'05.25" -
Thank you.
Kind regards..
-
Not strictly related to your question, but why are you doing this:
Set @tutar = @miktar*@bfiyat
Why not let the calling program do its own arithmetic?
With regard to your initial question, how can you guarantee that you will be getting only a scalar value returned? Have you tested the select statement to prove that you will only have one value returned?
-
I am trying to make this procedure with TSQL.
I want to learn.I want to improve my SQL programming. I am sure about getting only a scalar value returned.
Is it a wrong usage? I mean that it is better to
-
Well - do what you will while you're learning. That's how you learn after all
what I think he was getting at is that you're making 2 separate calls to the same table, pulling the same record, and pulling out one field each time. In a test DB with a few records - no issue; in a "production" DB with millions of records and users pounding on it - bad idea.
In SQL - if you can do it in one single call to the DB instead of 2, usually you go with the more "direct" route. It sets up fewer resources, etc...
It's kind of like the equivalent of building an object, pulling a property from it, then destroying it and recreating the EXACT same object all over again to pull another property. you might do that to learn, just not to "do" for real.
Of course - when trying something out - I've built the same kind of noodle logic code just to make sure I understand each step.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks for your warnings.
Can you give an example in connection with these warnings. I will understand better with an example.
-
To build on peter's example from before:
Set @sql=' SELECT '+ @fiyatgrup+'*'+ @fiyatgrup+'Brm' result FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+@urunid+' AND FiyatKategorisi='''+@ozelkategori+''''
CREATE TABLE #TEMP (i VARCHAR(200))
INSERT #Temp
EXEC(@sql)
select @tutar = i from #temp
Notice that we're now having the one call do the calculation using BOTH fields. now - if you're hell-bent on doing the calculation outside of the SQL call to the table, you could try:
Set @sql=' SELECT '+@fiyatgrup+','+@fiyatgrup+'Brm'+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+@urunid+' AND FiyatKategorisi='''+@ozelkategori+''''
CREATE TABLE #TEMP (i VARCHAR(200),j VARCHAR(200))
INSERT #Temp
EXEC(@sql)
select @bfiyat = i, @miktar=j from #temp
Select @tutar=@bfiyat*@miktar
Also - ideally - since we're doing math - the varchar(200) is not the right type (pick the appropriate type for the value
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thank you very much for your help.
-
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sel_genelsatifiyat2
(
@urunid int ,
@ozelkategori nvarchar(10),
@fiyatgrup nvarchar(30),
@miktar float,
@bfiyat float output,
@pbirim float output,
@tutar float output
 
AS
Declare @sql nvarchar(300)
Set @sql=' SELECT '+@fiyatgrup+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+convert(nvarchar,@urunid)+' AND FiyatKategorisi='''+@ozelkategori+''''
create table #temp(i int)
insert #temp
Execute (@sql)
Select @bfiyat =i from #temp
drop table #temp
Set @sql=null
Set @sql=' SELECT '+@fiyatgrup+'Brm'+' FROM dbo.GenelSFiyatlari'+
' WHERE UrunID = '+convert(nvarchar,@urunid)+' AND FiyatKategorisi='''+@ozelkategori+''''
create table #temp1(i int)
insert #temp1
Execute (@sql)
Select @pbirim =i from #temp1
drop table #temp1
Set @tutar = @miktar*@bfiyat
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply