Stored procedure looks right but dosn't give value

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)

Stored procedure looks right but dosn't give value

Author

Topic

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 03:21:38

Hello to all,

i'm new with SQL so please be patient with me.
i wrote a stored procedure that will be created dynamicaly and has a simple select.

when i'm running it, it takes the proper parameters but returns no rows.


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[BasicSearchProcedure] 

@NameTxt VarChar(50) ='%',

@MinTxt int=null,

@MaxTxt int=null,

@Nationality VarChar(50) ='%',

@P_Age  VarChar(50)='PlayerBasicInfoTable.Age' 

 

 

 as 

 

BEGIN

Declare @MyNewSql nvarchar(4000)

 

IF @MinTxt=NULL 

begin

SET @P_Age =' or PlayerBasicInfoTable.Age='+@MaxTxt

end

ELSE IF @MaxTxt=NULL 

begin

SET @P_Age =' or PlayerBasicInfoTable.Age='+@MinTxt;

end

 

set @MyNewSql='SELECT * FROM PlayerBasicInfoTable  WHERE Name ='+@NameTxt +'

  And Nationality = '+ @Nationality +  @P_Age 

 

 

Execute(@MyNewSql)

END

anyone have an idea?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 03:27:42

do a PRINT @MyNewSQL before execute to verify the query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 03:29:14

What result do you get if you replace EXEC (@MyNewSQL) with PRINT @MyNewSQL?
Do you get NULL?

Peter Larsson
Helsingborg, Sweden

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 03:36:42

WOW guys you are quick

, i appriciate it.

when i put PRINT nothing different happens, it acts the same as with EXEC and give no output.

where i can see the output?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 03:37:02

Also

1) Replace = NULL with IS NULL
2) Replace = '%' with LIKE '%'

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 03:38:44

If both @MinTxt and @MaxTxt is NULL nothing will be concatenated.
Please tell use what you really is doing.

What about @MinTxt and @MaxTxt both has value? For example 10 and 30.
Do you really only want to fetch all records = 10? Or all records = 30? Or all records BETWEEN 10 and 30?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 03:43:39

I don't think you need dynamic sql for this.
Please explain in plain english what you are trying to accomplish.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 03:54:16

Maybe this ?


CREATE PROCEDURE [dbo].[BasicSearchProcedure] 
	@NameTxt 	VarChar(50)     = null,
	@MinTxt 	int		= null,
	@MaxTxt 	int		= null,
	@Nationality 	VarChar(50)     = null
as 
BEGIN
        -- It is better than you explicitly specify column name to return
	select	*    
	from	PlayerBasicInfoTable
	where	[Name]		= coalesce(@NameTxt, [Name])
	and	Nationality	= coalesce(@Nationality, Nationality)
	and	Age		>= coalesce(@MinTxt, Age)
	and	Age		<= coalesce(@MaxTxt, Age)
END

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 03:54:59

What i'm doing basically is building a composite control that generates automatically the select and the stored procedure on the server.

in this example i have two costume TextBox control that searches in a DB a player by age column but if no age had entered then the procedure ignores the age params.

i took also in the consideration when @MixTxt and @MaxTxt are both null but for the simplicity i erased it from this example.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 03:56:08

try the query i posted. All input parameters can be NULL.

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 03:57:11

IsNull instaed of =null gives me error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 03:59:21

Peter is referring to


IF @MinTxt=NULL 
IF @MinTxt IS NULL

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 04:02:01

this procedure looks much better then mine and seems to do the same,
but still no values are returned.

CREATE PROCEDURE [dbo].[BasicSearchProcedure]
@NameTxt VarChar(50) = null,
@MinTxt int = null,
@MaxTxt int = null,
@Nationality VarChar(50) = null
as
BEGIN

select *
from PlayerBasicInfoTable
where [Name] = coalesce(@NameTxt, [Name])
and Nationality = coalesce(@Nationality, Nationality)
and Age >= coalesce(@MinTxt, Age)
and Age <= coalesce(@MaxTxt, Age)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 04:06:37

how do you call the stored procedure ?

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 04:09:57

hi KH,

i modified you query so:
alter PROCEDURE [dbo].[BasicSearchProcedure]
@NameTxt VarChar(50) = null,
@MinTxt int = null,
@MaxTxt int = null,
@Nationality VarChar(50) = null
as
BEGIN

select *
from PlayerBasicInfoTable
where [Name] = coalesce(@NameTxt, [Name])
and Nationality = coalesce(@Nationality, Nationality)
and Age >= coalesce(@MinTxt, Age)
or Age <= coalesce(@MaxTxt, Age)
END

and now it works .
but, i dont get the between affect with this change

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 04:12:53

What happened to the versatility of wildcard?

CREATE PROCEDURE dbo.BasicSearchProcedure
(
	@NameTxt VARCHAR(50) = '%',
	@MinTxt INT = NULL,
	@MaxTxt INT = NULL,
	@Nationality VARCHAR(50) = '%'
)
AS 

SELECT	*
FROM	PlayerBasicInfoTable
WHERE	[Name] LIKE ISNULL(@NameTxt, '%')
	AND Nationality LIKE ISNULL(@Nationality, '%')
	AND Age BETWEEN COALESCE(@MinTxt, Age) AND COALESCE(@MaxTxt, Age)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 04:13:16

changing to OR is not the right solution. How is your data like ? How do you called the stored procedure ?

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 04:21:18

the data is very simply and contains the proper fields.
Age is int
Nationality is varchar(50)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 04:36:53

Have you tried Peter's code ?
If it is still not working, better post the table DDL, some sample data and the expected result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 04:42:26

quote:
Originally posted by khtan

Post the table DDL, some sample data and the expected result.

This should be an automated SQLTeam forum respons for all posts!
Is this possible to incorporate?

Peter Larsson
Helsingborg, Sweden

lior3790
Starting Member

46 Posts

Posted - 2007-03-19 : 04:43:32

Peter's code dosen't retrieves nothing.

but thank you guys a lot, i can take it from here.
it was a good help forum.

until we meet again

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources