please help me to cearte this stored procedure

Hi ,

I want to make a report of records of a table, there is abut 15 fields that this report based on them , so we need a select query like this

Select f1,f2from Table1where f3=@.f3and f4=@.f4 and ….and f17=@.f17

-- f1 = field1 and ….

The problem is sometimes @.fs are empty, for example if @.f4was empty so "and f4=@.f4" should be excluded from the select query .(and it means there is no limitation for f4 field)

I know, probably I couldn't explain my purpose very well, but I hope somebody kindly try to understand it .

how can I perform that in a stored procedure?

Please help me

Thank you

try it like this:

SELECT f1,f2FROM Table1WHERE (f3=@.f3OR @.f3ISNULL)AND (f4=@.f4OR @.f4ISNULL)AND ….AND (f17=@.f17OR @.f17ISNULL)

|||

Is in your example @.f4 empty or null? I think null, so I created the following query for you:

Select

f1from table1where f1= @.f1and(f4= @.f4or @.f4isnull)

This query selects all the records where f1 matches @.f1 and f4 matches @.f4 or @.f4 is null (and will be ignored then).

I hope this helps

Richard

|||

mbanavige & richardsoeteman.net thank you very, very much!.

|||

Hi

Assume there are 3 tables like these:

Table0

Primary key

Name

1

Name1

2

Name2

3

Name3

Table 1:

Foreign key

Column1

1

Data1

2

Data1

Table2:

Foreign key

Column2

2

Data2

3

Data2

And there are 2 parameters that they may be null: @.Data1 and @.Data2

I need a select query that

-selects "Name2" from Table0 if:

@.Data1="Data1"

@.Data2="Data2"

-selects "Name1, Name2" from Table0 if:

@.Data1="Data1"

@.Data2=null

-selects "Name2, Name3" from Table0 if:

@.Data1=null

@.Data2="Data2"

And selects "Name1, Name2, Name3" from Table0 if both of@.Data1 &@.Data2 wasnull.

I know I did not explain very well again but it's the final step of my project and I really need help. So please help me again

Thanks,

|||

Same concept:

Read this post:http://forums.asp.net/thread/1440706.aspx

|||

thank you Mike,

What about parameters that areint ordecimal and we want to ignore them , they can not benull ,they can be 0.

thank you Mike,

|||

You can use the same concept

Select

f1from table1where(f1= @.f1or @.f1=0)

|||Thank you Richard,

Please help me out

I have written a sql procedure, i want to pass delared variable @.cnt in line131,char24
below is the PL Code:
CREATE PROCEDURE PL
@.dt varchar(10),
@.stock as bit --1/true for updated and 0/false for previous executed data
AS

set dateformat dmy
set nocount on

Declare @.sdate VARCHAR(10)
Declare @.clup as Decimal(18,2)
declare @.cnt as integer
DECLARE @.amt as decimal(18,2),@.gamt as decimal(18,2), @.damt as decimal(18,2), @.camt as decimal(18,2)

delete from temp_pl
delete from temp_bal

Select @.sdate = Convert(varchar(10),StartDate,103 ) From Param
exec show_trial @.dt
-- Now data is in temp_trial
if( @.Stock =1 )
begin
exec Closing @.sdate, @.dt
end

select party_nm as acc, dr_amt as Amtdr, cr_amt as Amtcr into #Tlist from temp_trial WHERE party_cd not in (150,152,151,38,36,33,32,35,34)
--select * from #tlist

SELECT Acc,AmtDr,AmtCr ,Type, odr into #tmpop FROM (
Select 'Opening Stock (UP)' as Acc ,'AmtDr'= case when oup >0 then oup else 0 end ,
'AmtCr'= case when oup <0 then abs(oup) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance
union
Select 'Opening Stock (Ex UP)' as Acc ,'AmtDr'= case when oex >0 then oex else 0 end ,
'AmtCr'= case when oex <0 then abs(oex) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance

union
Select 'Purchase (UP)' as Acc ,'AmtDr'= case when pup >0 then pup else 0 end ,
'AmtCr'= case when pup <0 then abs(pup) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Purchase (Ex UP)' as Acc ,'AmtDr'= case when pex >0 then pex else 0 end ,
'AmtCr'= case when pex <0 then abs(pex) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (UP)' as Acc , 'AmtDr' =case when sUp < 0 then abs(sup) else 0 END ,
'AmtCr' =case when sup > 0 then sup else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (Ex-UP)' as Acc , 'AmtDr' =case when sex < 0 then abs(sex) else 0 END ,
'AmtCr' =case when sex > 0 then sex else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance

union
Select 'Closing Stock(UP)' as Acc , 'AmtDr' =case when CUp < 0 then abs(cup) else 0 END ,
'AmtCr' =case when Cup > 0 then cup else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
union
Select 'Closing Stock(Ex-UP)' as Acc , 'AmtDr' =case when Cex < 0 then abs(cex) else 0 END ,
'AmtCr' =case when Cex > 0 then cex else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
)A

Select * into #tmpl from(
SELECT Acc, AmtDr, AmtCr ,Type ,odr
From (Select * from #tlist ) a INNER JOIN (Select party_nm,Ac_type as Type,odr From Party where Ac_type IN ('P','T') )
Acmast ON a.Acc = Acmast.Party_nm
UNION
select Acc,AmtDr,AmtCr ,Type , odr from #tmpop
) a

update #tmpl set Amtdr = abs(AmtCr) where AmtCr < 0
update #tmpl set AmtCr =abs( Amtdr) where AmtDr < 0
/*
--Gp
set @.clup = (Select 'GP' = case
when sum(AmtDr)>= sum(AmtCr) then (Sum(AmtDr) -Sum(AmtCr) )* -1

When sum(AmtDr) < Sum(AmtCr) then (Sum(AmtCr) -Sum(AmtDr) ) end From #tmpl where Type= 'T' )
*/
--TRADING A/C
SELECT Acc , AmtCr,type into #CT FROM #tmpL WHERE (AmtCr > 0 and Type ='T') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DT FROM #tmpL WHERE (AmtDr > 0 and Type ='T') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditT FROM #CT
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitT FROM #DT

Select IDENTITY(int, 1,1) AS sr_no, * INTO #TRADE1 from
(Select d.Acc as Accdr , d.AmtDr as AmtDr , C.acc as AccCr ,C.amtCr as AmtCr ,'T' as Type from
#debitT d Full outer join #creditT c on c.ids =d.ids
)A
insert into #trade1 values(null,0,null,0,'T') --Line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #trade1

select @.damt=sum(isnull(amtdr,0)) from #trade1
select @.camt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 values (null,@.damt,null,@.camt,'T')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 (accdr,amtdr,type) values('Gross Profit C/F to P&L',@.amt,'T')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
insert into #trade1 (acccr,amtcr,type) values('Gross Loss C/F to P&L',abs(@.amt),'T')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
end
insert into #trade1 values(null,0,null,0,'T') --Line after gross
insert into #trade1 values(null,@.gamt,null,@.gamt,'T')
insert into #trade1 values(null,0,null,0,'T') --Line after Trade gross
if @.amt>0
begin
insert into #trade1 (acccr,amtcr,type) values('Gross Profit B/F from P&L',@.amt,'P')
end
else if @.amt<0
begin
insert into #trade1 (accdr,amtdr,type) values('Gross Loss B/F from P&L',abs(@.amt),'P')
end
else
begin
insert into #trade1 values(null,null,null,null,'T')
end
Select * INTO #TRADE from #trade1 order by sr_no
------------------------------
--P/L A/C

SELECT Acc , AmtCr,type into #CP FROM #tmpL WHERE (AmtCr > 0 and Type ='P') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DP FROM #tmpL WHERE (AmtDr > 0 and Type ='P') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditP FROM #CP
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitP FROM #DP

select @.cnt=count(SR_NO)+1 from #trade

select IDENTITY (int,@.cnt,1) AS sr_no,accdr,amtdr,acccr,amtcr,type INTO #PL_TEMP1 from #trade t WHERE t.type='P'

insert into #pl_temp1 (accdr,amtdr,acccr,amtcr,type)
Select d.Acc ,d.AmtDr, C.acc ,C.amtCr as AmtCr ,'P' as Type from
#debitP d Full outer join #creditP c on c.ids =d.ids

delete from #trade WHERE type='P'
select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1

insert into #PL_TEMP1 values(null,0,null,0,'P') --line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1
insert into temp_bal values(@.amt)

select @.damt=sum(isnull(amtdr,0)) from #PL_TEMP1
select @.camt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 values (null,@.damt,null,@.camt,'P')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (accdr,amtdr,type) values('Net Profit C/F to Balance Sheet',@.amt,'P')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (acccr,amtcr,type) values('Net Loss C/F to Balance Sheet',abs(@.amt),'P')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
end
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after NET
insert into #PL_TEMP1 values(null,@.gamt,null,@.gamt,'P')
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after PL NET

Select * INTO #PL_TEMP from #PL_TEMP1 order by sr_no

INSERT INTO temp_PL
SELECT * FROM
(select * from #PL_TEMP
UNION
SELECT * FROM #TRADE
) a order by sr_no
GO

please help meDo not post your procedure. Just explain your problem in detail and post code in the pirticular error area.

Please help a noob out in stored procedure!

Hi All,

I'm a newbie in stored procedure programming. I wanted to learn how to perform keyword(s) search with stored procedures. Take the Pubs database for example. How can i create a stored procedure which takes in a string of keywords and return the title_id, title and notes column ? like if i pass in "computer easy" as keywords then the stored procedure should return all the rows with ANY of these keywords in those 3 columns. Can anyone give me some ideas on how to do this? like do i have to use dynamic sql?, any tutorials or sample codes? Thanks in advance!

regards

Download the latest Books On Line for SQL 2000 (or SQL 2005 as appropriate) from Microsoft website and check out for LIKE operator. What you need could be achieved by using LIKE. Try the samples and if you still cant get it working post the code here and we can help you out.
|||Hi ndinakar,
thanks for replying, do you have the links to where i can donwload those books that u mentioned thanks?
regards|||you can search on microsoft website ( or even google ?) . Thats what I would have done too.|||Hi ndinakar,
can you please go to my other posthttp://forums.asp.net/986519/ShowPost.aspx and see if u could answer my questions there as well? because i've posted for quite a while but still have not got a reply yet.

regards|||

hi ndinakar, i'm just trying out a really simple stored procedure but still couldn't get it to work dunno why? can you please help me thanks

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'test_sp' AND type = 'P')
DROP PROCEDURE test_sp
GO

USE pubs
GO

CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')

GO
The error message that i got was
Invalid column name 'string1'.

|||

kakusei wrote:

CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')

GO

Try this

You are creating a temporary table which might not exists when you query it. You table creation is correct. I tested it.
Just add a select command after the insert: select * from #temp
Thanks

|||

If you are not building any kind of index then you can use the like operator as shown below:

select * from <table_name> where title like '%computer easy%' or notes like '%computer easy%'
You can make pass the search string to a sp and then build the dynamic sql inside the sp. finally use the exec command to execute the dynamic sql:
Here is the sp:
create proc sp_TextSearch
@.sTextToSearch varchar(50)
as
declare @.sDynamicSQL varchar(400)
set @.sDynamicSQL = 'select * from <table_name> where title like''%'+@.sTextToSearch +'%'' or notes like ''%'+ @.sTextToSearch +'%'''
--print @.sDynamicSQL
exec(@.sDynamicSQL)

|||It worked for me. Is there anything specific you are trying to do ?|||

Hi ndinakar,

It worked for you ? but how come it doesn't work for me?? Well what i want to do is to create a stored procedure which will take in a keyword string as parameter then break up the string and store the keywords into the #temp table then after that with a while loop to loop through the temp table and dynamically build a sql statement to perform the search (dunno if that is possible yet). So now i'm just testing out how to create the #temp table first, but i couldn't even get that to work :(

i just got this error message

(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Procedure test_sp, Line 5
Invalid column name 'string1'.

|||

Hi musa,
Thanks for your reply and your sample code. But what i want to do ultimately is not just to return the records which only contain the keyword "computer easy" but to return the records which contains the keyword "computer" and "easy" and "computer easy" if i pass in the string "computer easy". So now i'm just thinking of a way to break up the keywords first and then make a loop to loop throught those keywords to build the search query dynamically. I'm think of breaking up the keyword string and storing it into a #temp table first. Dunno if i'm on the right track or not!? so that's why i'm trying to create the #temp table
but couldn't even get that to work

|||

Ok now when i renamed the #temp table to #temp1
but then i put a select * from #temp1 after the insert it didn't show anything
It should show the string1 and string2 right??

|||Sorry all,
forget about my last post about it didn't show anything. i'm stupid forgot to run the procedure!
So now i will have to try and pass in a keyword parameter and break that up and store it into the temp table so that if i pass in "computer easy"
it will store it in the temp table like
keyword id | keyword
--------
1 | computer
2 | easy
any suggestions on how to do that?|||

Here's a function that will take a delimited string and split it and return the values in a table.
--------
CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END
-----------------
After you create the function you can use it as :
Declare @.list varchar(200)

set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))

|||

Hi ndinakar,
can you please see what's wrong with my code below thanks, i can't get it to work. And thanks for your code, but i wanted to do something like the full-text search Contains clause. So i think the code would not work for me

set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))

--------------------
MY CODE
-----------------------
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'keyword_search' AND type = 'P')
DROP PROCEDURE keyword_search
GO
USE pubs
GO

CREATE PROCEDURE keyword_search
@.keywords varchar(200)

AS

CREATE TABLE #keyword_table
(keyword_index tinyint IDENTITY(1,1) NOT NULL PRIMARY KEY, keyword varchar(200), key_param varchar(5))

DECLARE @.sql nvarchar(1000)
DECLARE @.keyword_count tinyint
DECLARE @.paramlist nvarchar(1000)

declare @.1 varchar(200)
declare @.2 varchar(200)
declare @.3 varchar(200)
declare @.4 varchar(200)
declare @.5 varchar(200)
declare @.6 varchar(200)
declare @.7 varchar(200)
declare @.8 varchar(200)
declare @.9 varchar(200)
declare @.10 varchar(200)

BEGIN
DECLARE @.delimiter char(1)
DECLARE @.temp_keywords varchar(200)
DECLARE @.keyword_length smallint
DECLARE @.id varchar(5)

SET @.delimiter = ','
SET @.temp_keywords = @.keywords
SET @.keyword_count = 0

WHILE CHARINDEX(@.delimiter, @.temp_keywords) > 0
BEGIN
SELECT @.keyword_length = CHARINDEX(@.delimiter, @.temp_keywords)
INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
SELECT @.temp_keywords = SUBSTRING(@.temp_keywords, @.keyword_length + 1, LEN(@.temp_keywords))
select @.keyword_count = @.keyword_count + 1
END

INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
select @.keyword_count = @.keyword_count + 1

select @.sql = 'select title, notes from pubs..titles '

IF @.keyword_count = 3
BEGIN
select @.sql = @.sql + 'where title like ' + quotename('%' + @.1 + '%','''')
+ ' or notes like ' + quotename('%' + @.1 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.2 + '%','''')
+ ' or notes like ' + quotename('%' + @.2 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.3 + '%','''')
+ ' or notes like ' + quotename('%' + @.3 + '%','''')
-- select @.sql = @.sql + 'where title like''%@.1%'' or notes like''%@.1%'' and '
-- select @.sql = @.sql + 'title like''%@.2%'' or notes like''%@.2%'' and '
-- select @.sql = @.sql + 'title like''%@.3%'' or notes like''%@.3%'''
END

select @.paramlist ='@.1 varchar(200),
@.2 varchar(200),
@.3 varchar(200)'

DECLARE keyword_cursor CURSOR
FOR SELECT keyword FROM #keyword_table
OPEN keyword_cursor
FETCH NEXT FROM keyword_cursor into @.1
FETCH NEXT FROM keyword_cursor into @.2
FETCH NEXT FROM keyword_cursor into @.3

END

exec sp_executesql @.sql, @.paramlist, @.1, @.2, @.3

GO

EXEC keyword_search 'computer,easy,user'

Please Help ... Can't Figure This One Out

Greetings,
I need to write a SQL stored procedure to accomplish the following and I'm
not having any luck. So that I don't confuse anyone too much, I'll leave out
the code I'm trying and just explain what I'm trying to do.
Start with
Table: sequenceOrder
orderNumber totalUnits
101 26
102 11
103 9
104 8
105 16
I have a VB.NET program that will be passing in two parameters, one is
workDays (number of work days during a given w) and the other is
dailyUnits (maximum number units to be made during each wday).
I need a running total, by record line, for the totalUnits. If the running
total is less than dailyUnits, then I need to add the current value for that
work day. If the running total is greater than dailyUnits, then I need to
add 1 to the current value for that work day. Also, I need to number the
orders within that workDay.
Using the sample table above and having workDays = 3 and dailyUnits = 30 the
output table would like this:
Finish with
Table: sequenceOrder
orderNumber totalUnits workDay sequenceOrder
101 26 1 1
102 11 2 1
103 9 2 2
104 8 2 3
105 16 3 1
106 12 3 2
Is this possible, if so how do I get this done?
Thanks for all the help,
James Walker, Jr."James Walker" <walker@.modernfold.com> wrote in message
news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> I need to write a SQL stored procedure to accomplish the following and I'm
> not having any luck. So that I don't confuse anyone too much, I'll leave
> out the code I'm trying and just explain what I'm trying to do.
> Start with
> Table: sequenceOrder
> orderNumber totalUnits
> 101 26
> 102 11
> 103 9
> 104 8
> 105 16
> I have a VB.NET program that will be passing in two parameters, one is
> workDays (number of work days during a given w) and the other is
> dailyUnits (maximum number units to be made during each wday).
> I need a running total, by record line, for the totalUnits. If the running
> total is less than dailyUnits, then I need to add the current value for
> that work day. If the running total is greater than dailyUnits, then I
> need to add 1 to the current value for that work day. Also, I need to
> number the orders within that workDay.
> Using the sample table above and having workDays = 3 and dailyUnits = 30
> the output table would like this:
> Finish with
> Table: sequenceOrder
> orderNumber totalUnits workDay sequenceOrder
> 101 26 1 1
> 102 11 2 1
> 103 9 2 2
> 104 8 2 3
> 105 16 3 1
> 106 12 3 2
> Is this possible, if so how do I get this done?
>
Ok, this is a fun one. Here's a simple solution, using intiger division of
the running total by the number of hours per day. But I worry that the
assignment of orders to days is too "dumb". I have a suspicion that there's
a better solution somewhere that would sequence the work to fill up the days
and minimize idle time.
create table Orders
(
OrderNumber int primary key,
TotalUnits int not null
)
insert into Orders(orderNumber,TotalUnits)
select 101,26
union all select 102,11
union all select 103,9
union all select 104,8
union all select 105,16
go
declare @.days int,
@.dailyUnits int
set @.days = 3
set @.DailyUnits = 30;
with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
(
select OrderNumber, TotalUnits,
(select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
RunningTotal
from Orders o
)
select
Ordernumber,
TotalUnits,
1+RunningTotal/@.DailyUnits WorkDay,
row_number() over (partition by RunningTotal/@.DailyUnits order by
OrderNumber) SequenceOrder
from UpcomingOrders
where 1+RunningTotal/@.DailyUnits <= @.days
David|||David,
Thanks for the reply. It works great. Also, "filling up idle time" is in the
next phase. As in my example, the first day has 4 more units it could make,
so I will need to search down the records where the totalUnits is <= 4 and
then move that order up for the w.
James Walker, Jr.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JMsfHCgGHA.452@.TK2MSFTNGP02.phx.gbl...
> "James Walker" <walker@.modernfold.com> wrote in message
> news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Ok, this is a fun one. Here's a simple solution, using intiger division
> of the running total by the number of hours per day. But I worry that the
> assignment of orders to days is too "dumb". I have a suspicion that
> there's a better solution somewhere that would sequence the work to fill
> up the days and minimize idle time.
>
> create table Orders
> (
> OrderNumber int primary key,
> TotalUnits int not null
> )
> insert into Orders(orderNumber,TotalUnits)
> select 101,26
> union all select 102,11
> union all select 103,9
> union all select 104,8
> union all select 105,16
> go
> declare @.days int,
> @.dailyUnits int
> set @.days = 3
> set @.DailyUnits = 30;
>
> with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
> (
> select OrderNumber, TotalUnits,
> (select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
> RunningTotal
> from Orders o
> )
> select
> Ordernumber,
> TotalUnits,
> 1+RunningTotal/@.DailyUnits WorkDay,
> row_number() over (partition by RunningTotal/@.DailyUnits order by
> OrderNumber) SequenceOrder
> from UpcomingOrders
> where 1+RunningTotal/@.DailyUnits <= @.days
>
> David
>

Please help ...

Hello,

Could somebody please tell me , how to export stored procedure data to EXCEL using DTS ?

Please advice me!Your stored procedure is probably returning a a set of records?

In that case, just take the query in your storder proc and create a DTS job to run that query into an excel file. If you need to then be able to kick off that job at will, you can use the DTSRUN utility from a stored procedure to start it.

If you need to send variables to the DTS job so that it can dynamically select only certain records, you can pass parameters to a stored procedure and then use techniques discussed here:

http://www.databasejournal.com/features/mssql/article.php/1461481
http://www.databasejournal.com/features/mssql/article.php/1462571

You were a little sketchy on what you need to do.

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou can create the temp table and insert the execution of the dts package into the temp table. This will "capture" the results and effectively mask them from being seen as output. You will also probably want to use SET NOCOUNT ON in your procedure.

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou'd be more likely to get an answer to this question in the MS-SQL forum (http://www.dbforums.com/f7).

-PatPsql

Please help

I have a certain problem that I'm going to try and demonstrate using the northwind
database as an example.

I'm trying to write a stored procedure in SQL Server 2000, I am using the products table and
the order details table.

The first procedure I wrote uses a left outer join to show all the products and if the product
doesn't show up in the order details table it will show up with a zero no problem the stored procedure
is as follows and works great.

ALTER PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName

Now I change the stored procedure to filter the second table by an orderid and now the only products
that show up are the products that exist in the second table.

My question is how do I show all the products like in the first stored procedure
and filter the stored procedures second table and still show all the products
including the ones that don't show up.

I wrote the second stored procedure and like I said it only shows the products that show up in the second table.

ALTER PROCEDURE dbo.StoredProcedure2
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

HAVING (dbo.[Order Details].OrderID = 10248)

I would really appreciate if somebody codes demonstrate to me how to do this
thanks in advance.This is how it hsould be:

SELECT dbo.Products.ProductID, dbo.Products.ProductName,COUNT(dbo.[Order Details].ProductID)AS[in orders]
FROM dbo.ProductsLEFTOUTERJOIN
dbo.[Order Details]ON dbo.Products.ProductID= dbo.[Order Details].ProductID
WHERE dbo.[Order Details].OrderID= 10248
GROUPBY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

|||Thank you very much for your quick response ndinakar,
but the procedure you wrote gives the same result as the second procedure
I wrote.

What I wanted to do was show all the products that exist in the products table
like in the first procedure which shows all the products and products that don't show up
in the second table show up with a zero I would like to do the same thing but when I filter
the second table by the orderId the only result I get is the products that show up in the second table not all the products.

How can I show all the products from the products table
and still filter the productID in the second table|||Of course it only shows products from the second table. Your WHERE clause tells it to only show specific products from the second table.

Your question makes little sense...|||I showed have explained what I was trying to do in the first place,

I've written a program in ASP.NEV VB I have a drop-down list in a data grid
the drop-down list contains instructors names and the value contains their ID

The drop-down list data source is the instructors table each time and instructor makes a dive
it goes into the orders table by the instructors ID.

What I was trying to do is get a list of all the instructors and their ID and see how many times they show up
in the orders table for certain dates and arrange the drop-down list according to the amount of dive's
the instructor made during that date.

----------Instructors table----------

Inst_Idint40
Inst_Full_Namenvarchar500
Activebit10

-------Orders Table --------------

Order_Idint40
Activity_Idint40
Inst_Idint41
Client_Namenvarchar500
Telnvarchar301
Paymentint40
Voucher_Nonvarchar501
Agent_Idint41
Reservation_Noint41
Worker_Idint41
Order_Date_Timedatetime80
Equipmentnvarchar2|||Try something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, ISNULL(COUNT(dbo.[Order Details].ProductID),0 AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)

This will give you a 0 value for products with no corresponding orders.

Does this help, or am I still misunderstanding the requirements?|||OK, I see what I was missing. That OrderDate requirement.

No problem. Just do your OUTER JOIN to an inline table (or subquery).

LEFT OUTER JOIN
 (SELECT ProductID, DetailID
 FROM [Order Details]
 WHERE OrderDate = @.OrderDate) AS OrderDetails

Now just treat your virtual OrderDetails table the same as you would your real OrderDetails table. The rest of the query should be the same.|||I didn't exactly understand how to do it I am quite new to SQL Server
I tried copying your script like so

CREATE PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails

GO

But it did not work i probably don't understand how to make an inline table
if you could just give me a little more help thanks a lot|||SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(OrderDetails.DetailID) AS InOrders
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)|||I tried using the procedure you wrote and then tried it with my actual table
and each time I get the same error incorrect Syntex neither the keywords 'group'
do you have any suggestions but I've also attached my actual procedure.

---------------------------

ALTER PROCEDURE dbo.GetInstructors
(@.Id int)
AS

SELECT dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name, COUNT(dbo.Orders_Sub.Inst_Id) AS Expr1
FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
GROUP BY dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name
ORDER BY (3)

---------------------------

I really appreciate the help you giving me thank you in advance again.|||Sorry. I left out the join condition:

FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
ON dbo.Instructors.Inst_Id = Orders_Sub.Inst_Id

|||Thank you very much for your help it works like a dream.

please help

hi all, i have stored procedure returen an integer how i can take this integer (c#). thanksHi,

Create a command object and add a parameter to the Parameters collection, marked as a return value. Something like this:

SqlParameter param = new SqlParameter("@.ret_val", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
myCmd.Parameters.Add(param);

Then make the call to the stored proce, and read the value of @.ret_val (or whatever you call it):

int32 ret = myCmd.Parameters("@.ret_val").Value;

I typed this from memory, so the code might require tweaking.

Don|||Hi, i want to returen one value (integer) from table to send this value to anather waiting quary how i can do this (in aspx page with c#) . thanks|||From my last post, take the value in ret and pass it to a new query.

Is that what you mean? Or what am I not understanding?

Donsql

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
.. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana M
Hi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please Help

I am trying to pull the last 30 records in a table. I'm trying to write a
dynamic stored procedure in order to do this. I am joining a couple tables
and the table i need to get the 30 records out of is the second table. I
can't seem to get it to work with my code. Below is the code that I am using
to try to do this, I have declared the variables earlier in this procedure:
(Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
From dbo.schedulegovcom
Where LotID<=@.HighNum
and >=@.LowNum
Group by LotID) pTry:
SELECT TOP 30
[field list]
FROM
dbo.schedulegovcom
ORDER BY
LotID DESC
Mike
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am
> using
> to try to do this, I have declared the variables earlier in this
> procedure:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||You can not mix resultset with assigning value to a variable in the same
select statement.
-- wrong
select @.i = orderid, customerid from dbo.orders
AMB
"A.B." wrote:

> I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am usi
ng
> to try to do this, I have declared the variables earlier in this procedure
:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||Thanks that worked
"Mike Jansen" wrote:

> Try:
> SELECT TOP 30
> [field list]
> FROM
> dbo.schedulegovcom
> ORDER BY
> LotID DESC
> Mike
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that proble
m.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your s
p
from vb. also take a look at sysprocesses able and try and see if you have a
n
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If
I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it doe
s
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running fr
om
> query analyzer
> I can assure you that it is not permission problem, it is not query proble
m,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored proce
dues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are calle
d
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:
> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>sql

Please Check my store Procedure !

I have a store Procedure modify structTable but check syntax is error !

Please help me ?
--
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
ALTER TABLE @.TABLE_NAME ALTER @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
ELSE
BEGIN
ALTER TABLE @.TABLE_NAME ADD @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
END

-

CREATE PROCEDURE ENCRYPTION getString()

AS
SELECT * FORM CUSTOMER

-

Funtion getString() return a string is " With Encryption"?

Do I call function getString() in here ?

You need to use dynamic sql for this.

Code Snippet

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
exec ('ALTER TABLE '+@.TABLE_NAME+' ALTER '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
ELSE
BEGIN
exec('ALTER TABLE '+@.TABLE_NAME+' ADD '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
END

The other proc is also incorrect. It should be like so.

Code Snippet

CREATE PROCEDURE getString()
with ENCRYPTION
AS
SELECT * FROM CUSTOMER

|||Thanks Oj my idea is not clearly ! I have a Store procedure :
--
Create procedure MyEnCryption With Encryption
AS
Begin
Select * from Customer
End

I want replace string " With Encryption" with calling Function GetString(); Function GetString() return a string is "With Encryption"

this store rewrite :
-
Create procedure MyEnCryption GetString()
AS
Begin
Select * from Customer
End

--

Is this store run ?|||

What you're trying to do is not possible. The syntax to create a procedure is like so:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

|||

While creating the objects you have to use the proper syntax what is already defined..

You can achive your desired output if you create your object using dynamic SQL...

But it is not recommanded to do this.. & I didn’t understand why you need this logic..

Note:

When you encrypt the procedure/function/view/trigger you have to store your soruce code on filesystem or source safe for future reference, you never get back the soruce text from the SQL Server. So, it is really bad idea to have dynamic sql to create encrypted objects.

Code Snippet

Declare @.sql as varchar(8000)

Set @.sql = 'Create procedure MyEnCryption ' +GetString() + '

AS

Begin

Select * from Customer

End'

exec (@.sql)

|||

Thanks Admin !

Now I'am understading !

Please Advice !

Hi,

I am trying to solve this procedure.

Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.

With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!

How can I solve this.... whether I can solve this with CURSOR or I have to use someother way...Please advice me !

DATA
---
DEGREE_EARNED
-------
EMPID DEGREEID
-- ----
201 12
201 3
201 250
202 3
202 10
203 17

DEPARTMENT
-----
DEPID DEGREEID
-- ----
10 1 12
111 250
111 12
121 3
121 12
121 250
-----------



---------------------
DECLARE @.vchid int
DECLARE testcursor CURSOR FOR

SELECT degree_id
FROM degree_earned WHERE emp_id= @.empid

OPEN testcursor
FETCH NEXT FROM testcursor INTO @.vchid

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN
Select @.outresult = COUNT(*)
from
department
where degree_id = @.vchid

FETCH NEXT FROM testcursor INTO @.vchid
END
---------------------Can somebody help me,please (:|||So is what you want something like:EMPID DEGREEID DEPT_COUNT
-- --- ----
201 12 2
201 3 1
201 250 2
202 3 1
202 10 0
203 17 0I don't understand exactly what you want, so I can't be much help until I do. Sorry!

-PatP|||Hey Pat,

Thats what i wanted...Can u please show me , how to solve this...Thanks!|||USE Northwind
GO

CREATE TABLE DEGREE_EARNED(EMPID int, DEGREEID int)
CREATE TABLE DEPARTMENT(DEPID int, DEGREEID int)
GO
INSERT INTO DEGREE_EARNED(EMPID, DEGREEID)
SELECT 201, 12 UNION ALL
SELECT 201, 3 UNION ALL
SELECT 201, 250 UNION ALL
SELECT 202, 3 UNION ALL
SELECT 202, 10 UNION ALL
SELECT 203, 17

INSERT INTO DEPARTMENT(DEPID, DEGREEID)
SELECT 101, 12 UNION ALL
SELECT 111, 250 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 121, 3 UNION ALL
SELECT 121, 12 UNION ALL
SELECT 121, 250
GO

SELECT A.DEGREEID, ISNULL(ROW_OCCURS,0)
FROM ( SELECT DISTINCT DEGREEID FROM DEGREE_EARNED) AS A
LEFT JOIN ( SELECT DEGREEID, COUNT(*) AS ROW_OCCURS
FROM DEPARTMENT GROUP BY DEGREEID) AS B
ON A.DEGREEID = B.DEGREEID
GO

--DROP TABLE DEGREE_EARNED
--DROP TABLE DEPARTMENT
GOsql

Plase help

Hello,
I have a developer who developed the following stored procedure.
My question is since it started with a Begin Transaction and if the record
exists in the EXISTS statement and it will return an ID without commit /
rollback, does he need to either commit or rollback the transaction?
begin transaction
if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDate
= convert(char(11),getdate(),106))
Begin
Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
CreateDate = convert(char(11),getdate(),106)
Return @.OpportunityId
End
else
Begin
Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
If @.@.Error <> 0
Begin
Rollback Transaction
Return -1
End
RETURN SCOPE_IDENTITY( )
Commit Transaction
End
GOEd,
Try moving the BEGIN TRAN down in the code to just prior the DML (INSERT)
statment.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BA6A49DF-BC78-412D-BC86-9D456BFDDBE1@.microsoft.com...
> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the
> record
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||There is only one insert statement that I can see. Transaction is not
necessary at all in this case.
It is also not necessary to perform the same query two times in a row...
select the value once, then check to see if it is null.
SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
CreateDate = convert(char(11),getdate(),106)
IF @.OpportunityID IS NOT NULL Return @.OpportunityId
ELSE
BEGIN
INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
IF @.@.Error <> 0 RETURN -1
ELSE RETURN SCOPE_IDENTITY()
END
END
GO
John Scragg
"Ed" wrote:

> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the reco
rd
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDa
te
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||However, the is a risk that the row is deleted after the SELECT and before t
he INSERT. To eliminate
that risk, isolation SERIALIZABLE should be used, which requires a transacti
on (to do its work)...
:-)
Also, the code doesn't catch if the SELECT returns several rows. This is why
I prefer SET @.var =
(SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
> There is only one insert statement that I can see. Transaction is not
> necessary at all in this case.
> It is also not necessary to perform the same query two times in a row...
> select the value once, then check to see if it is null.
> SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
> AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
> CreateDate = convert(char(11),getdate(),106)
> IF @.OpportunityID IS NOT NULL Return @.OpportunityId
> ELSE
> BEGIN
> INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> IF @.@.Error <> 0 RETURN -1
> ELSE RETURN SCOPE_IDENTITY()
> END
> END
> GO
> John Scragg
> "Ed" wrote:
>|||Tibor, you make some good points. I am afraid I was not as clear and the
formatting of SQL in the newsgroup does not help :-) Let me try to be more
clear.
The original proc was apparently designed to perform 1 of 2 action. Either
(1) return the ID of an existing row, or (2) in the absance of a row, insert
a new row and return the ID of the new row.
If the row is deleted after the select it should not matter (as far as the
proc is concerned) since there is nothing else happening. If the select
returns a value, the proc returns the coulmn value selected and the INSERT
statement is ignored. The way it was written originally there were two
identical selects. First to check IF EXISTS and then the same query to get
the value. This did provide a tiny window for a row to be deleted between
the two selects, but my opinion was that it is simply ineffcient to query
twice for the same result :-)
As for the possible error on multiple rows, you are absolutely right. It
can error. It is the same as the developer's original code, and so I am
assuming that they have confirmed the values in the where clause will provid
e
a unique row (through constraints, rules, etc.). If not, then he has this
problem in his original code as well.
John
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||That is a good point that I never think about...
How am I able to control/put the Transaction to even lock the Select
statement to make sure when the record/ID is returned, there is no one else
delete the record already?
Thanks
Ed
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||Sorry, John. I misinterpreted the logic. It looked like me as if the row exi
sts, get the values
*and* do an insert. I didn't read the original code carefully enough. I catc
hed that you picked up
on the "read twice" issue, but thought that a hole was left as I assumed ano
ther logic. My bad...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:7C58CD9A-0D47-4D4D-A68B-15F5D64D65B2@.microsoft.com...
> Tibor, you make some good points. I am afraid I was not as clear and the
> formatting of SQL in the newsgroup does not help :-) Let me try to be mor
e
> clear.
> The original proc was apparently designed to perform 1 of 2 action. Eithe
r
> (1) return the ID of an existing row, or (2) in the absance of a row, inse
rt
> a new row and return the ID of the new row.
> If the row is deleted after the select it should not matter (as far as the
> proc is concerned) since there is nothing else happening. If the select
> returns a value, the proc returns the coulmn value selected and the INSERT
> statement is ignored. The way it was written originally there were two
> identical selects. First to check IF EXISTS and then the same query to ge
t
> the value. This did provide a tiny window for a row to be deleted between
> the two selects, but my opinion was that it is simply ineffcient to query
> twice for the same result :-)
> As for the possible error on multiple rows, you are absolutely right. It
> can error. It is the same as the developer's original code, and so I am
> assuming that they have confirmed the values in the where clause will prov
ide
> a unique row (through constraints, rules, etc.). If not, then he has this
> problem in his original code as well.
> John
> "Tibor Karaszi" wrote:
>

Planning on going with dynamic SQL, but...

I've read a few posts on the stored procedure vs dynamic sql debate. I ran a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored procs and just write the SQL inside inside our functions in our business layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have to keep up with all of them per all of our functions that call them.

Thanks,
Ron

That is sometimes a 'heated' discussion. How it goes usually depends upon whether the speakers background comes from the development world or the database world.

The Developer wants full and unfettered access to the data in order to easily create an application to solve a problem. The DBA wants to protect the data at all costs.|||

Your tests may indicate a wash but SQL Server cannot cache a query plan for embedded SQL.

It isn't scalable.

Dynamic SQL has god-awful performance issues.

I cannot believe there is even a debate in this forum surrounding embedded SQL. i've read some of the other posts - and i can tell you that the reason DBAs do not like and frequently don't even allow dynamic/embedded SQL is because THEY KNOW MORE ABOUT SQL SERVER than developers. I couldn't write a connection string in C## to save my behind, but I can tell you that you should not under any circumstances use embedded SQL, and I know this from 10+ years of SQL Server experience. I've seen it used to hack. I've seen it bring a server to it's knees.

perhaps the developers who favor embedded or dynamic SQL have never monitored the SQL server during the use of such code. (?)

once you get actual data and multiple users hitting your database, in a real world environment, you WILL NOT see a 'wash' on performance.

The security issues are even worse. No organization that goes through even minimal security audits will allow embedded SQL in an application.

SQL Server may appear to be a fairly user friendly database, and it has a lot of stuff built in that makes it seem as if almost anyone can be a DBA. This is misleading. It is not just another MS Access "database".

If I were you, I would listen to actual DBAs on this issue.

Placing sp_executesql data into a var

I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :

lookup_table1
table1_id
description

lookup_table2
table2_id
description

This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.

Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.

This procedure as it stands right now

CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS

declare @.sql nvarchar(2048)

--
-- Check if the given lookup value exists in the give table / column
--
set @.sql ='SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value + ''''
EXECUTE sp_executesql @.sql
if (@.@.rowcount = 0) goto new_value
if (@.@.error <> 0) goto on_error
return(1)

--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @.sql = 'INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @.sql
if (@.@.error <> 0) goto on_error
commit transaction
return(1)

--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO

The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.

My attempted solution was to declare a @.temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.

declare @.temp int
...
SET @.temp = EXECUTE sp_executesql @.SQL
if (@.@.temp = null) goto new_value
SELECT @.temp
return(1)

Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciatedHow about

CREATE TABLE #temp(Result vachar(8000))

INSERT INTO #temp(Result) EXEC(@.SQL)|||If it is at all possible I would like to avoid using temporary tables, as if I can do this with SQL memory objects the performance hit would (I assume be much lower)|||Your worried about performance, yet your using dynamic sql...hmmmm|||Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.|||I'm not sure exactly what your looking for...but here are three versions...

DECLARE @.sql nvarchar(4000), @.x int, @.y int, @.z int

PRINT 'Something that will work'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z

PRINT 'Something that will return 0 rows'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Brett' + ''''
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z

PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z|||Originally posted by Brett Kaiser
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
[/code]

Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)|||Originally posted by Enigma
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)

I'll see if I can dig up an 8086 for you...I'm sure it'll be an improvement...

Did you want 1 or 2 5 1/4 drives?

And I think there's a huge 250k chip in there...

Black and green are you ok, right?|||Brett .. no need to ... i ve already got a 8086 with 1 MB RAM ...
dont bother yourself with the 256 K .. right now I am trying to get Windows 3.11 loaded on it .. help me out there if you can :)|||Is that vanilla or windows for workgroups?

and a meg...wow...

HUGE|||Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :

CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS

declare @.procedure_sql nvarchar(2048)

set @.procedure_sql = '

declare @.sql nvarchar(2048)

set @.sql = (SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value +''')

if (@.sql = null)
begin
begin transaction
INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + ''')
SELECT SCOPE_IDENTITY()
if (@.@.error <> 0) goto on_error
commit transaction
return

on_error:
rollback transaction
return
end
else
begin
SELECT @.sql
end
return'
print @.procedure_sql;

EXEC( @.procedure_sql )
return(1)
GO|||You can return values from executesql by OUTPUT semantics on fully declared parameters:

declare @.sql nvarchar(4000)
declare @.params nvarchar(4000)

set @.sql = N'select @.OUT=@.IN * 2'

set @.params = N'@.in int, @.out int OUTPUT'

declare @.in int
declare @.out int

set @.in = 4

exec sp_executesql @.sql, @.params, @.in, @.out OUTPUT

select @.out

=8