Dynamic statement in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic statement in stored procedure

Author

Topic

Excorcist
Starting Member

1 Post

Posted - 2011-01-25 : 10:29:07

Hello everyone

I'm relatively new to Sql server, and I would like to learn more.
Trying to create completely flexible stored procedure using Dynamic statements.

I have a handful of tables:

CREATE TABLE [dbo].[Mesec](
[MesecID] [int] IDENTITY(1,1) NOT NULL,
[Mesec] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Mesec] PRIMARY KEY CLUSTERED
(
[MesecID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Godina](
[GodinaID] [int] IDENTITY(1,1) NOT NULL,
[Godina] [int] NULL,
CONSTRAINT [PK_Godina] PRIMARY KEY CLUSTERED
(
[GodinaID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Osoba](
[OsobaID] [int] IDENTITY(1,1) NOT NULL,
[OsobaIme] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OsobaPrezime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Osoba] PRIMARY KEY CLUSTERED
(
[OsobaID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Firma](
[FirmaID] [int] IDENTITY(1,1) NOT NULL,
[FirmaNaziv] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OsobaID] [int] NULL,
CONSTRAINT [PK_Firma] PRIMARY KEY CLUSTERED
(
[FirmaID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Firma] WITH CHECK ADD CONSTRAINT [FK_Firma_Osoba] FOREIGN KEY([OsobaID])
REFERENCES [dbo].[Osoba] ([OsobaID])
GO
ALTER TABLE [dbo].[Firma] CHECK CONSTRAINT [FK_Firma_Osoba]

CREATE TABLE [dbo].[Faktura](
[FakturaID] [int] IDENTITY(1,1) NOT NULL,
[Datum] [datetime] NULL,
[FirmaID] [int] NULL,
[UkupniFakturisaniIznos] [decimal](18, 2) NULL,
CONSTRAINT [PK_Faktura] PRIMARY KEY CLUSTERED
(
[FakturaID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Faktura] WITH CHECK ADD CONSTRAINT [FK_Faktura_Firma] FOREIGN KEY([FirmaID])
REFERENCES [dbo].[Firma] ([FirmaID])
GO
ALTER TABLE [dbo].[Faktura] CHECK CONSTRAINT [FK_Faktura_Firma]

CREATE TABLE [dbo].[GodinaMesec](
[GodinaMesecID] [int] IDENTITY(1,1) NOT NULL,
[GodinaID] [int] NULL,
[MesecID] [int] NULL,
CONSTRAINT [PK_GodinaMesec] PRIMARY KEY CLUSTERED
(
[GodinaMesecID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[GodinaMesec] WITH CHECK ADD CONSTRAINT [FK_GodinaMesec_Godina] FOREIGN KEY([GodinaID])
REFERENCES [dbo].[Godina] ([GodinaID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[GodinaMesec] CHECK CONSTRAINT [FK_GodinaMesec_Godina]
GO
ALTER TABLE [dbo].[GodinaMesec] WITH CHECK ADD CONSTRAINT [FK_GodinaMesec_Mesec] FOREIGN KEY([MesecID])
REFERENCES [dbo].[Mesec] ([MesecID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[GodinaMesec] CHECK CONSTRAINT [FK_GodinaMesec_Mesec]

CREATE TABLE [dbo].[GodinaMesecFirma](
[GodinaMesecFirmaID] [int] IDENTITY(1,1) NOT NULL,
[FirmaID] [int] NULL,
[GodinaMesecID] [int] NULL,
[Sumarno] [decimal](30, 2) NULL,
CONSTRAINT [PK_GodinaMesecFirma] PRIMARY KEY CLUSTERED
(
[GodinaMesecFirmaID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[GodinaMesecFirma] WITH CHECK ADD CONSTRAINT [FK_GodinaMesecFirma_Firma] FOREIGN KEY([FirmaID])
REFERENCES [dbo].[Firma] ([FirmaID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[GodinaMesecFirma] CHECK CONSTRAINT [FK_GodinaMesecFirma_Firma]
GO
ALTER TABLE [dbo].[GodinaMesecFirma] WITH CHECK ADD CONSTRAINT [FK_GodinaMesecFirma_GodinaMesec] FOREIGN KEY([GodinaMesecID])
REFERENCES [dbo].[GodinaMesec] ([GodinaMesecID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[GodinaMesecFirma] CHECK CONSTRAINT [FK_GodinaMesecFirma_GodinaMesec]

I'm building stored procedure:

CREATE PROCEDURE [dbo].[GodinaMesecFirmaGet]
@DatumOd datetime,
@DatumDo datetime,
@OsobaID int,
@FirmaID int
AS
BEGIN
SET NOCOUNT ON;
Declare @GodinaOd int,
@GodinaDo int,
@Godina int,
@MesecOd int,
@MesecDo int,
@MesecGornji int,
@MesecDonji int,
@SQLDynamic NVARCHAR(1024),
@SQLDynamic1 NVARCHAR(1024),
@SQLDynamicFirma NVARCHAR(1024),
@SQLOrderby NVARCHAR(1024),
@SQLRedovi NVARCHAR(1024)

Set @MesecGornji = 12
Set @MesecDonji = 1
Set @GodinaOd = (Select datepart(year,@DatumOd))
Set @GodinaDo = (Select datepart(year,@DatumDo))
Set @MesecOd = (Select datepart(month,@DatumOd))
Set @MesecDo = (Select datepart(month,@DatumDo))

Set @SQLDynamic = '

Select gmf.*, g.Godina, m.Mesec, o.OsobaIme , o.OsobaPrezime, fi.FirmaNaziv
from GodinaMesecFirma gmf inner join godinamesec gm on gmf.GodinaMesecID = gm.GodinaMesecID inner join Godina g on gm.GodinaID = g.GodinaID inner join Mesec m on gm.MesecID = m.MesecID inner join Firma fi on fi.FirmaID = gmf.FirmaID inner join Osoba o on fi.OsobaID = o.OsobaID
where ('
if (@GodinaOd = @GodinaDo)
Begin
Set @SQLDynamic = @SQLDynamic + '(g.Godina = ' + convert (varchar, @GodinaOd) +' and m.MesecID between ' + convert (varchar, @MesecOd) + ' and '+ convert (varchar, @MesecDo) +')'
Set @SQLDynamic1 = ')'
End
Else
Begin
Set @SQLDynamic = @SQLDynamic + '(g.Godina = ' + convert (varchar, @GodinaOd) +' and m.MesecID between ' + convert (varchar, @MesecOd) + ' and '+ convert (varchar, @MesecGornji) +')'
Set @SQLDynamic1 = ' or (g.Godina = ' + convert (varchar, @GodinaDo) +' and m.MesecID between ' + convert (varchar, @MesecDonji) + ' and '+ convert (varchar, @MesecDo) +'))'
End
Set @SQLOrderby = '
Order by g.Godina, m.MesecID'
Set @Godina = @GodinaOd + 1
if (@GodinaDo - @GodinaOd > 1)
While (@GodinaDo - @Godina >= 1)
Begin
Set @SQLRedovi = ' or (g.Godina = ' + convert (varchar, @Godina) + ' and m.MesecID between 1 and 12)'
Set @Godina = @Godina + 1
Set @SQLDynamic = @SQLDynamic + @SQLRedovi
End
Set @SQLDynamicFirma = ''
if (@FirmaID IS NOT NUll)
if (@FirmaID <> -1)
Set @SQLDynamicFirma = ' and fi.FirmaID = ' + convert (varchar, @FirmaID) + ' '
Set @SQLDynamic = @SQLDynamic + @SQLDynamic1 + @SQLDynamicFirma + @SQLOrderby
print @SQLDynamic
exec (@SQLDynamic)
END

It can handle whatever parameters I give it.
I would also like to be able to search using varchar parameters but I don't seem to find a way to insert single quotation (') in my where clause. Is there any way to resolve this?
I would like to add something like:
@SqlFirmaNaziv = ' and fi.FirmaNaziv = ' + " @FirmaNaziv"
and add it to
@SQLDynamic
Thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-25 : 11:15:40

quote:
Trying to create completely flexible stored procedure using Dynamic statements

Hate to tell you this but that kind of thing completely defeats the purpose of stored procedures. If you really need this flexibility then just construct the SQL in your application. Be careful of SQL injection (sanitize input, check syntax, etc.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources