October 8, 2010 at 8:59 am
Hello.
Rather than write an app I thought I could quickly code up a report that had all the fields of a little table as parameters. The last parameter would be an instruction - so the user could enter "dave smith", "8 oct 10", "31 dec 3000", "FIND" and it would report back all the "Dave Smiths" (simplified version - my versions has extra fields). If Dave Smith decided to leave tomorrow then the user could type 'Dave Smith', '8 oct 10', '9 oct 10', 'AMEND' and the report would amend the record and report back the amended record. It would do something similar for INSERT.
The problem I had was trying to put all 3 select statements into one report - I wasn't sure it could be done so I created a function (using IF statements on the instruction parameter). Which seemed to work well - until I actually tried to amend or insert a new record. Annoyingly Functions aren't allowed to update tables. I tried using Dynamic SQL and hiding the SQL in sprocs but no luck.
I know using an App would be the correct way, but I do like finding cheap, quick wins and the users are already used to running reports.
My question is - is it possible to write a sproc that will run different pieces of code depending on a parameter value (will always return data in the same structure)? Has anyone done anything similar?
Cheers,
Warren
October 8, 2010 at 10:30 am
Hello.
I was a bit of an idiot there. Tried to do it in a Function when I could have just used the IFs in a SPROC. I put the function into a sproc, got rid of a couple of lines and it worked. Users now have a consistent interface to getting reports and amending/inserting data (for one table).
If anyone's interested the sproc is below:
USE [MISStaging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[usp_FlagTableOperations] (
@v1 /*@process */ varchar(25) , @v2 /*@FlagName */ varchar(50), @v3 /*@flagvalue */ varchar(50), @v4 /*@PartOfProject */ varchar(50)
, @v5 /*@ValidFrom */ datetime, @v6 /*@ValidTo */ datetime, @v7 /*@Owner */ varchar(50), @v8 /*@Description */ varchar(200)
)
AS
BEGIN -- function
-- find existing records, insert a new record, update old record if exists
declare @nSql nvarchar(1000) -- used for the Amend and Insert commands
if (@v1 = 'Find')
begin
if (select count(*) from misstaging..flagtable
where flagname like isnull(@v2,'%')
and flagvalue like isnull(@v3,'%')
and partofproject like isnull(@v4,'%')
and validfrom = isnull(@v5,validfrom)
and validto = isnull(@v6,validto)) > 0
begin
select 'Found' Process , FlagName , flagvalue , PartOfProject , ValidFrom , ValidTo , [Owner] , [Description] from misstaging..flagtable
where flagname like isnull(@v2,'%')
and flagvalue like isnull(@v3,'%')
and partofproject like isnull(@v4,'%')
and validfrom = isnull(@v5,validfrom)
and validto = isnull(@v6,validto)
return
end
else
begin
select 'ERROR - Not Found' Process , @v2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
end -- find
if (@v1 = 'Insert')
begin
if (select count(*) from misstaging..flagtable
where flagname like isnull(@v2,'%')
and flagvalue like isnull(@v3,'%')
and partofproject like isnull(@v4,'%')
and (@v5 between validfrom and validto or @v6 between validfrom and validto)) > 0
begin
select 'ERROR - can`t insert' , @v2, @v3, @v4, @v5, @v6, @v7, @v8
union
select 'Conflicts' Process , FlagName , flagvalue , PartOfProject , ValidFrom , ValidTo , [Owner] , [Description] from misstaging..flagtable
where flagname like isnull(@v2,'%')
and flagvalue like isnull(@v3,'%')
and partofproject like isnull(@v4,'%')
and (@v5 between validfrom and validto or @v6 between validfrom and validto)
return
end
else-- didn't find any conflicts so if all details are there insert it
begin
if (@v2 is null or @v3 is null or @v4 is null or isdate(@v5)=0 or isdate(@v6)=0 or @v8 is null)
begin
select 'ERROR - empty fields' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
else
begin
SET @nSql = 'insert into misstaging..flagtable (flagname, flagvalue, partofproject, validfrom, validto, [owner], [description]) '+
'values ('''+@v2 +''','''+@v3 +''','''+@v4 +''','''+cast(@v5 as nvarchar(30)) +''','''+cast(@v6 as nvarchar(30)) +''','''+@v7 +''','''+@v8 +''')'
Exec sp_executeSQl @nSql
select 'Inserted' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
end
return
end -- insert
if (@v1 = 'Amend') -- only allowed to amend the dates or description!!
begin
if (select count(*) from misstaging..flagtable
where flagname = @v2 and flagvalue = @v3 and partofproject =@v4 and (validfrom = @v5 or validto = @v6)) = 0
begin
select 'ERROR - Record not Found' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
else
begin
if (select count(*) from misstaging..flagtable
where flagname = @v2 and flagvalue = @v3 and partofproject =@v4
and @v5 <= validto and @v6 >= validfrom
) > 1
begin -- found more than one = error
select 'ERROR - too many records' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
union
select 'Conflicts' Process , FlagName , flagvalue , PartOfProject , ValidFrom , ValidTo , [Owner] , [Description] from misstaging..flagtable
where flagname = @v2 and flagvalue = @v3 and partofproject =@v4
and @v5 <= validto and @v6 >= validfrom
return
end
else
begin
if (select count(*) from misstaging..flagtable
where flagname = @v2 and flagvalue = @v3 and partofproject =@v4
and @v5 <= validto and @v6 >= validfrom
) = 1
begin -- found one - ok
SET @nSql = ' update misstaging..flagtable set validfrom = '''+ cast(@v5 as nvarchar(30)) +''', validto = '''+ cast(@v6 as nvarchar(30)) + ''', [owner] = '''+ @v7 +
''', [description] = '''+ @v8 +'''where flagname = '''+ @v2 +''' and flagvalue = '''+ @v3 +
''' and partofproject = '''+ @v4 +''' and '''+ cast(@v5 as nvarchar(30)) +''' <= validto and '''+ cast(@v6 as nvarchar(30)) +''' >= validfrom'
Exec sp_executeSQl @nSql
select 'Amended' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
else
begin -- found zero = error
select 'ERROR - check dates' , @V2, @v3, @v4, @v5, @v6, @v7, @v8
return
end
end
end
end -- amend
return
END -- function
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply