RE: SQL Stored Proc recieving a var

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 11/05/04 

Date: Fri, 5 Nov 2004 09:48:01 -0800

Try,

...
and charindex(@type, [name]) > 0
...

AMB

"Travis Falls" wrote:

> I am working on this stored proc listed below. I am trying to make 3 sp
> into by passing in the @type var which is = to Billing, System, and Log.
> When I hardcode say '%Log%' in the place of the var it works when I pass
> it in it doesn't but no error. Does anyone see anything jumping out at
> them? Is there a way to use Quotename and have it wrap the var with the
> '% and %'? Any pointers to make this better would help. Thanks
> everyone!
>
>
>
> CREATE PROCEDURE dbo.PROC_getView
> @type varchar(6) = null
>
> AS
> SET NOCOUNT ON
>
> DECLARE @tableName VARCHAR(100)
>
> DECLARE @sql NVARCHAR(1000)
> SET @sql = ''
> PRINT(@type)
> DECLARE @mySelect VARCHAR(23)
> SET @mySelect = 'SELECT DISTINCT * FROM '
>
> DECLARE tableNamesCursor CURSOR
> FOR
> SELECT name
> FROM
> sysobjects as s
> where
> xtype = 'U'
> and name like + "'%" + @type + "%'"
> and datediff(year, crdate, getdate()) < 1
> order by
> crdate asc
>
> OPEN tableNamesCursor
>
> - Rugby
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>