Is dynamic SQL the way to go? - 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 2000 Forums

Transact-SQL (2000)

Is dynamic SQL the way to go?

Author

Topic

elpuerco
Starting Member

10 Posts

Posted - 2007-01-03 : 04:01:42

Hi, I have heard/read that dynamic SQL should be avoided whenever possible but struggle to see an alternative to the solution.

Here is the problem:

A user can pass a parameter to search for bins depending on the first three characters.

So if the user enters 10 the sp needs to return all bins that begin with 10. 10A01, 10A02, 10A03 etc...

If they enter 100 the sp needs to return all bins that begin with 100. 100A76, 10A77, 10A78 etc...

If I dont use the dynamic SQL below if the users enters 10 and I use 'LIKE '10%' the sp will return every bin starting with 10 so all the 100, 101, 102 etc etc bins also get returned. @BinNo is set elsewhere to the users input so if 10 it is set to 10% and if 100 then 100%

The fact the SQL code works means it is right..right? But to me it is wrong as it looks messy and feel there must be a more elegant way than this?

Any advise gratefully received.....thanks


SELECT @SQLString = 'INSERT INTO #Temp (bin_no, location) SELECT bin_no, location FROM #Bins WHERE bin_no LIKE ' + 
        CASE LEN(@BinNo) 
             WHEN 3 THEN + '''' + @BinNo + '''' + ' AND ISNUMERIC(SUBSTRING(bin_no, 3, 1))=0' 
             WHEN 4 THEN + '''' + @BinNo + '''' + ' AND ISNUMERIC(SUBSTRING(bin_no, 2, 1))=1' 
         END
				
exec(@SQLString)

Kristen
Test

22859 Posts

Posted - 2007-01-03 : 04:06:50

Would this do? :


INSERT INTO #Temp (bin_no, location) 
SELECT bin_no, location 
FROM #Bins 
WHERE bin_no = @BinNo OR bin_no LIKE @BinNo + '[^0-9]%'

Kristen

elpuerco
Starting Member

10 Posts

Posted - 2007-01-03 : 06:00:27

Hi, no alas it does not.

When I run this for say the parameter 10 it returns all bins that begin with the charcters 10.

So I get 10A01, 100A89, 101B23 etc etc returned

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 06:16:41

Why? It works fine for me...


declare @bin varchar(10)

set @bin = '10'

select * from
	(select '10A01' bin_no union all
	select '10A02' union all
	select '10A03' union all
	select '100A89' union all
	select '101B23' union all
	select '100A76') t
WHERE bin_no = @Bin OR bin_no LIKE @Bin + '[^0-9]%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elpuerco
Starting Member

10 Posts

Posted - 2007-01-03 : 06:22:40

Aha and so it does for me when I remove the bit of code which I used to tag '%' onto the end of @Bin earlier in my code.

Now that is what I call elegant

Thanks Kristen....tiptop

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 09:45:21

Note that ISNUMERIC is not always reliable
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

elpuerco
Starting Member

10 Posts

Posted - 2007-01-03 : 10:07:16

Madhivanan, I get a 'service not available' when I try that link

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-03 : 15:24:03

Nice one K!

For some special characters isnumeric returns 1, eg:

select	char(163),isnumeric(char(163))
	,char(164),isnumeric(char(164))

---- ----------- ---- ----------- 
£    1           ¤    1

rockmoose

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-03 : 20:35:26

Think that's bad? Try a little scientific notation with ISNUMERIC...

SELECT ISNUMERIC('10E5')
SELECT ISNUMERIC('10D5')

--Jeff Moden

elpuerco
Starting Member

10 Posts

Posted - 2007-01-04 : 09:38:55

Dudes.....ya losing me

elpuerco
Starting Member

10 Posts

Posted - 2007-01-04 : 09:43:06

Here is another one...

can the method of [^0-9]% be used to extract the integer part of a number that is in a varchar field?

for example:

10
11.0000000
5.000000
6
-23

I want 10, 11, 5, 6 and -23.

I have tried using a cast to integer but this reduces the query output, cast(qty as int)? if I just have qty in the select I get all the correct data but with all the .00000

I've also used a conveluted patindex + substring combo which is just plan ugly.....oh and doesn't work!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 10:23:35

1
Select cast(No as int) from table
WHERE bin_no not like '%[A-Za-z]%'

2 Refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

3 Wait Until Kristen, Rocky, Jeff Moden or Peso gives better solution

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 11:40:10

Select cast(No as int) from table
WHERE bin_no not like '%[^0-9.]%'

might be "tighter" - it ignores space though - which would probably only CAST to INT if it was leading/trailing rather than embedded

WHERE RTrim(LTrim(bin_no)) not like '%[^0-9.]%'

would solve that - but it requires a function call on the column which will kill any index usage.

Other than that perhaps this:


DECLARE @TestData TABLE
(
	StringValue	varchar(10)
)

INSERT INTO @TestData
SELECT 'XXX' UNION ALL
SELECT 'X' UNION ALL
SELECT '' UNION ALL
SELECT NULL UNION ALL
SELECT '-23-10-22' UNION ALL
SELECT '23.10.22' UNION ALL
SELECT '.23' UNION ALL
SELECT '.23.10.22' UNION ALL
SELECT '10' UNION ALL
SELECT '11.0000000' UNION ALL
SELECT '5.000000' UNION ALL
SELECT '6' UNION ALL
SELECT '-23'

SELECT	StringValue, 
	LEFT(StringValue, PATINDEX('%[^0-9-]%', StringValue + '.')-1)
FROM @TestData

The '-23-10-22' example behaves badly and might need a special-case workaround.

The all-invalid-characters cases return empty string, if you prefer them to return NULL instead then use:

NullIf(LEFT(StringValue, PATINDEX('%[^0-9-]%', StringValue + '.')-1), '')

instead

Kristen

elpuerco
Starting Member

10 Posts

Posted - 2007-01-05 : 03:58:37

SELECT StringValue,
LEFT(StringValue, PATINDEX('%[^0-9-]%', StringValue + '.')-1)
FROM @TestData

Works perfectly for what I need, thanks once again....tiptop

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-05 : 07:38:16

Thanks for the info Kristen

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources