SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamic SQL problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tiwas
Starting Member

Norway
37 Posts

Posted - 05/15/2011 :  14:46:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/16/2011 :  06:22:06  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/16/2011 :  06:53:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

tiwas
Starting Member

Norway
37 Posts

Posted - 05/16/2011 :  13:37:49  Show Profile  Reply with Quote
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. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000