correct my stored procedure

[former_member197621]

Dear all

I want to make project field as mandatory in all document for that i write this following stored procedure

no error in my stored procedure, but it is not working.

*******************************

Step : 1 Declare all variable

Step : 2

select @object_type = 22

select @table = case @object_type

when '22' then 'POR1'

when '15' then 'ODLN'

when '16' then 'ORDN'

when '13' then 'OINV'

when '14' then 'ORIN'

end

Step :3

set @CheckValue = 'SELECT min(isnull(Project, '')) FROM' + @table + 'where DocEntry =

@list_of_cols_val_tab_del'

Step :4

if (@CheckValue = '')

begin

SET @error_message = N'Project code Or Dimension Missing'

SET @error = 1

end

select @error, @error_message

************************

No error showing for this, but its allowing to add purchase order with out 'project code"

If i change step 3 as following its working correctly

set @CheckValue = (SELECT min(isnull(PO.Project, '')) FROM PCH1 PO where PO.DocEntry =

@list_of_cols_val_tab_del)

but i don't want to give table name directly in select statement

[Former Member · Accepted solution]

No error message may not prove it is working. You can not only use line table without header to establish an object.

[former_member197621 · Accepted solution]

hai Gordon

Ok thanks for your valuable answer

can u correct me where i want to change in my code for make a project field as mandatory in all document

[Former Member · Accepted solution]

The problem is, that the query isnt running, when you cocatenate it as strings. For running this type of queries you must use

execute sp_executesql @sql

but there is problem with setting the value to variable.-The easiest way for you is create case when sentence and for each type of documet write it directly somethink as

declare @table varchar(50)
declare @checkvalue varchar(50)
declare @object_type integer
select @object_type = 22
select @table = case @object_type
when '22' then 'OPOR'
when '15' then 'ODLN'
when '16' then 'ORDN'
when '13' then 'OINV'
when '14' then 'ORIN'
end 

select @checkvalue = 
case @table
 when 'OPOR' then 
(SELECT min(isnull(Project, '')) FROM opor where DocEntry = @list_of_cols_val_tab_del)
when 'ODLN' then
(SELECT min(isnull(Project, '')) FROM odln where DocEntry = @list_of_cols_val_tab_del)
when 'ORDN' then
(SELECT min(isnull(Project, '')) FROM ordn where DocEntry = @list_of_cols_val_tab_del)
when 'OINV' then
(SELECT min(isnull(Project, '')) FROM oinv where DocEntry = @list_of_cols_val_tab_del)
when 'ORIN' then
(SELECT min(isnull(Project, '')) FROM orin where DocEntry = @list_of_cols_val_tab_del)
end

if (@CheckValue = '')
begin
SET @error_message = N'Project code Or Dimension Missing' 
SET @error = 1 
end
select @error, @error_message

[former_member197621 · Accepted solution]

hai Petr Verner

I understood in your answer value is not setting for the variable,thanks for your valuable replay ,am going to use your coding.but is there any other way to write the code in single line using @table insist of using table name directly, like

set @CheckValue = 'SELECT min(isnull(Project, '')) FROM'+ @table + ' where DocEntry = @list_of_cols_val_tab_del'

execute sp_executesql @CheckValue

i checked this lines same problem happen value is not set for @CheckValue is there any way to set value for @CheckValue

[Former Member · Accepted solution]

Yes, you could use

execute sp_executesql 'SELECT @CheckValue = min(isnull(Project, '')) FROM'+ @table + ' where DocEntry = @list_of_cols_val_tab_del'

[former_member197621 · Accepted solution]

hai i tested that query but its showing error message called

Msg 102, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 68

Incorrect syntax near '+'.

please correct that query

[Former Member · Accepted solution]

for example

declare @sql as nvarchar(500)
set @sql = 'IF OBJECT_ID(''tempdb..##tmp'') IS NOT NULL 	begin	drop table ##tmp	end SELECT   min(isnull(Project, '''')) as x into ##tmp FROM '+ @table + ' where DocEntry = ' + convert(varchar(50), @list_of_cols_val_tab_del)

execute sp_executesql  @sql
select @checkvalue = x from ##tmp

I tried it and it works

[former_member197621 · Accepted solution]

hai Petr Verner

Thanks a lot ,you solved my problem, am really very happy for your all valuable golden replaies

And one more doubt what is the use of "@list_of_cols_val_tab_del" please tell me with good example

Edited by: Prasanna s on Dec 22, 2008 1:07 PM

[Former Member · Accepted solution]

in @list_of_cols_val_tab_del variable is often stored docentry for documents or cardcode for master data.

[Former Member]

Because I don't have any data to test, sorry for not able to help. What you can do might be to split complete conditions one by one. That is a good way to troubleshoot.

[Former Member]

At least, when '22' then 'POR1' is wrong. You can only use OPOR but not POR1.

Thanks,

Gordon

[former_member197621]

hai Gordon

If i use this line insist of that its working correctly.

set @CheckValue = (SELECT min(isnull(PO.Project, '')) FROM POR1 PO where PO.DocEntry =

@list_of_cols_val_tab_del)

Edited by: Prasanna s on Dec 22, 2008 9:22 AM