Dynamic SQL problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Dynamic SQL problem

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2011-05-15 : 14:46:01

Hi guys,

I'm trying to make a dynamic sql statement, but it doesn't work. (In the code pasted under this I've only worked with the first statement, so the rest of the variables aren't correctly set in the sp_executesql statement). I reckon I will manage the rest if someone could just help me past the first issue(s) in the "fornavn" part. I would also love to hear it if there's a better way of tackling this problem.

Even though the printed statement looks right, I get the following when printing it out and then executing:

USE [Maria]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[Maria_SearchMedlem]
                @fornavn = N'la'--,
                --@fornavn_eksakt = NULL,
                --@etternavn = NULL,
                --@etternavn_eksakt = NULL

SELECT  'Return Value' = @return_value

GO

SELECT     Medlemmer.medlemsnummer, Medlemmer.fornavn, Medlemmer.etternavn, Medlemmer.adresse1, Medlemmer.adresse2, Medlemmer.postnummer, 
                      Medlemmer.sted, Medlemmer.tittel, Medlemmer.fdato, Medlemmer.epost, Medlemmer.tlf_priv, Medlemmer.tlf_jobb, Medlemmer.tlf_mob, 
                      Medlemmer.utmeldt_bevis, Medlemmer.utmeldt, Medlemmer.levert_emblem, Medlemmer.død, Medlemmer.død_melding, Medlemmer.død_status, 
                      Medlemmer.ordensgrad, Land.land
FROM         Medlemmer LEFT OUTER JOIN
                      Land ON Medlemmer.land = Land.id WHERE 1=1  and fornavn like '%' + @fornavn_input + '%'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

(1 row(s) affected)

To me, that looks pretty valid...Could someone please point out the problem with that?

The whole sproc is like this:

USE [Maria]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Maria_SearchMedlem] 
@medlemsnummer int = null,
@fornavn nvarchar(100) = null,
@fornavn_eksakt bit = null,
@etternavn nvarchar(100) = null,
@etternavn_eksakt bit = null,
@adresse1 nvarchar(MAX)  = null,
@adresse2 nvarchar(MAX)  = null,
@postnummer nchar(10)  = null,
@sted nvarchar(50)  = null,
@land int  = null,
@tittel nvarchar(MAX)  = null,
@fdato_fra date = null,
@fdato_til date = null,
@epost nvarchar(MAX)  = null,
@tlf_priv nvarchar(20)  = null,
@tlf_jobb nvarchar(20)  = null,
@tlf_mob nvarchar(20)  = null,
@utmeldt_bevis bit  = null,
@utmeldt_fra date  = null,
@utmeldt_til date  = null,
@levert_emblem bit = null,
@dod_fra date = null,
@dod_til date = null,
@dod_melding_fra date = null,
@dod_melding_til date = null,
@dod_status nvarchar(MAX)  = null,
@ordensgrad_fra date = null,
@ordensgrad_til date = null
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

declare @base_query nvarchar(max)
set @base_query = '
SELECT     Medlemmer.medlemsnummer, Medlemmer.fornavn, Medlemmer.etternavn, Medlemmer.adresse1, Medlemmer.adresse2, Medlemmer.postnummer, 
                      Medlemmer.sted, Medlemmer.tittel, Medlemmer.fdato, Medlemmer.epost, Medlemmer.tlf_priv, Medlemmer.tlf_jobb, Medlemmer.tlf_mob, 
                      Medlemmer.utmeldt_bevis, Medlemmer.utmeldt, Medlemmer.levert_emblem, Medlemmer.død, Medlemmer.død_melding, Medlemmer.død_status, 
                      Medlemmer.ordensgrad, Land.land
FROM         Medlemmer LEFT OUTER JOIN
                      Land ON Medlemmer.land = Land.id'
  
declare @params nvarchar(max)
set @params = ' WHERE 1=1'                    

if ((@fornavn is not null) AND (@fornavn <> ''))
        begin
                if @params <> '' set @params = @params + ' and '
                if @fornavn_eksakt = 1 set @params = @params + 'fornavn = @fornavn_input'
                else set @params = @params + 'fornavn like ' + char(39) + '%' + char(39) + ' + @fornavn_input + ' + char(39) + '%' + CHAR(39)
        end
if ((@etternavn is not null) AND (@etternavn <> ''))
        begin
                if @params <> '' set @params = @params + ' and '
                if @etternavn_eksakt = 1  set @params = @params + 'etternavn = \@etternavn'
                else set @params = @params + 'etternavn like ' + char(39) + '%' + char(39) + ' + @etternavn_input + ' + char(39) + '%' + CHAR(39)
        end

if @adresse1 is not null 
        begin
                if @params <> '' set @params = @params + ' and '
                set @params = @params + 'adresse1 like ' + char(39) + '%' + char(39) + ' + @adresse1_input + ' + char(39) + '%' + CHAR(39)
        end
if @adresse2 is not null 
        begin
                if @params <> '' set @params = @params + ' and '
                set @params = @params + 'adresse2 like ' + char(39) + '%' + char(39) + ' + @adresse2_input + ' + char(39) + '%' + CHAR(39)
        end
        
if @postnummer is not null
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'postnummer = @postnummer'
        end

if @sted is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'sted like ' + char(39) + '%' + char(39) + ' + @sted_input + ' + char(39) + '%' + CHAR(39)
        end

if @land is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'land like ' + char(39) + '%' + char(39) + ' + @land_input + ' + char(39) + '%' + CHAR(39)
        end

if @tittel is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'tittel like ' + char(39) + '%' + char(39) + ' + @tittel_input + ' + char(39) + '%' + CHAR(39)
        end

if @fdato_fra is not null
        begin
                if @params <> '' set @params = @params + ' and '                
                if @fdato_til is not null set @params = @params + 'fdato > @fdato_fra AND fdato < @fdato_til'
                else set @params = @params + 'fdato = @fdato_fra'
        end

if @epost is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'epost = @epost'
        end

if @tlf_priv is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'tlf_priv = @tlf_priv'
        end

if @tlf_mob is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'tlf_mob = @tlf_mob'
        end

if @tlf_jobb is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'tlf_jobb = @tlf_jobb'
        end

if @utmeldt_bevis is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'utmeldt_bevis = @utmeldt_bevis'
        end

if @utmeldt_fra is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                if @utmeldt_til is not null set @params = @params + 'utmeldt > @utmeldt_fra and utmeldt < @utmeldt_til'
                else set @params = @params + 'utmeldt = @utmeldt_fra'
        end

if @levert_emblem is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + 'levert_emblem = @levert_emblem'
        end

if @dod_fra is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                if @dod_til is not null set @params = @params + 'død > @dod_fra and død < @dod_til'
                else set @params = @params + 'død = @dod_fra'
        end

if @dod_melding_fra is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                if @dod_melding_til is not null set @params = @params + 'død_melding > @dod_melding_fra and død_melding < @dod_melding_til'
                else set @params = @params + 'død_melding = @dod_melding_fra'
        end

if @ordensgrad_fra is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                if @ordensgrad_til is not null set @params = @params + 'ordensgrad > @ordensgrad_fra and ordensgrad < @ordensgrad_til'
                else set @params = @params + 'ordensgrad = @ordensgrad_fra'
        end

if @dod_status is not null 
        begin
                if @params <> '' set @params = @params + ' and '                
                set @params = @params + '@død_status like ' + char(39) + '%' + char(39) + ' + @dod_status_input + ' + char(39) + '%' + CHAR(39)
        end

declare @query nvarchar(max)
set @query = @base_query + @params
print @query
execute sp_executesql @statement = @query, @params = N'@fornavn_input nvarchar(100),@medlemsnummer,
@fornavn,
@fornavn_eksakt,
@etternavn,
@etternavn_eksakt,
@adresse1,
@adresse2,
@postnummer,
@sted,
@land,
@tittel,
@fdato_fra,
@fdato_til,
@epost,
@tlf_priv,
@tlf_jobb,
@tlf_mob,
@utmeldt_bevis,
@utmeldt_fra,
@utmeldt_til,
@levert_emblem,
@dod_fra,
@dod_til,
@dod_melding_fra,
@dod_melding_til,
@dod_status,
@ordensgrad_fra,
@ordensgrad_til', @fornavn_input = @fornavn;
END

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-16 : 06:22:06

you have the equivalent of
declare @sql nvarchar(1000), @a varchar(100)
select @a = 'a'
select @sql = 'select x=1, y=1 where ''a'' like ''%'' + @a + ''%'''
exec sp_executesql @sql, N'@a varchar(10),@x,@y', @a=@a

whereas you probably want something like
declare @sql nvarchar(1000), @a varchar(100)
select @a = 'a'
select @sql = 'select x=1,y=1 where ''a'' like ''%'' + @a + ''%'''
declare @t table (x int, y int)
insert @t exec sp_executesql @sql, N'@a varchar(10)', @a=@a
select * from @t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-16 : 06:53:08

or maybe

declare @sql nvarchar(1000), @a varchar(100), @x int, @y int
select @a = 'a'
select @sql = 'select @x=1, @y=1 where ''a'' like ''%'' + @a + ''%'''
exec sp_executesql @sql, N'@a varchar(10),@x int out,@y int out', @a, @x out, @y out
select @x, @y

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tiwas
Starting Member

37 Posts

Posted - 2011-05-16 : 13:37:49

Wow...thanks, but that was *way* over my head. At least now that I just came home from a full body workout. I will have to reread that when I'm done being queezy ;)

Unless there's some easier way to put it. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources