String checking

I am creating a select statement based on a passed variable.

I want to check the string to see if it contains a field from the table
and if so add it to my select statement. Is there a function that I can use
to check for my field

I.e something like

Declare @sql varchar(1000)
Declare @mystring varchar( 500)

Set @mystring = 'abc'
Set @sql = "Select '

if contains(@mystring, abc) Set @sql = @sql + 'abc'

Hi Paul,

Charindex , should do the job you want

Dave

Paul wrote:
> I am creating a select statement based on a passed variable.
>
> I want to check the string to see if it contains a field from the table
> and if so add it to my select statement. Is there a function that I can use
> to check for my field
>
> I.e something like
>
> Declare @sql varchar(1000)
> Declare @mystring varchar( 500)
>
> Set @mystring = 'abc'
> Set @sql = "Select '
>
> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>
>
>

Thanks that does it.

Whilst I have been doing that , I have constructed a create #tbl string, and
executed it successfully, but the table does not exist when I try to select
from it.

I.e.
Set @sql = 'CREATE TABLE #tblMAIN (
[STAGE_1_START] [datetime] NULL ,
[ELEMENT] [varchar] (40) NULL
ON [PRIMARY]'

Exec(@sql)

Should this work, or is this not possible?

Paul


"dave ballantyne" wrote in message
news:uxrg%23ZOCJHA.1228-at-TK2MSFTNGP02.phx.gbl...
Hi Paul,

Charindex , should do the job you want

Dave

Paul wrote:
> I am creating a select statement based on a passed variable.
>
> I want to check the string to see if it contains a field from the table
> and if so add it to my select statement. Is there a function that I can
> use to check for my field
>
> I.e something like
>
> Declare @sql varchar(1000)
> Declare @mystring varchar( 500)
>
> Set @mystring = 'abc'
> Set @sql = "Select '
>
> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>
>
>

Paul
declare @col as sysname

set @col='opendate'

If exists (
select * from sys.columns

where object_id=object_id('tblname')

and name =@col

)
................





"Paul" wrote in message
news:vfydnRrvksd0wSvVnZ2dnUVZ8sDinZ2d-at-bt.com...
>I am creating a select statement based on a passed variable.
>
> I want to check the string to see if it contains a field from the table
> and if so add it to my select statement. Is there a function that I can
> use to check for my field
>
> I.e something like
>
> Declare @sql varchar(1000)
> Declare @mystring varchar( 500)
>
> Set @mystring = 'abc'
> Set @sql = "Select '
>
> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>
>
>

Hi,

That will work , and does work, but the #table is being drop when it
falls out of scope, the exec statement.

Dave

Paul wrote:
> Thanks that does it.
>
> Whilst I have been doing that , I have constructed a create #tbl string, and
> executed it successfully, but the table does not exist when I try to select
> from it.
>
> I.e.
> Set @sql = 'CREATE TABLE #tblMAIN (
> [STAGE_1_START] [datetime] NULL ,
> [ELEMENT] [varchar] (40) NULL
> ON [PRIMARY]'
>
> Exec(@sql)
>
> Should this work, or is this not possible?
>
> Paul
>
>
> "dave ballantyne" wrote in message
> news:uxrg%23ZOCJHA.1228-at-TK2MSFTNGP02.phx.gbl...
> Hi Paul,
>
> Charindex , should do the job you want
>
> Dave
>
> Paul wrote:
>> I am creating a select statement based on a passed variable.
>>
>> I want to check the string to see if it contains a field from the table
>> and if so add it to my select statement. Is there a function that I can
>> use to check for my field
>>
>> I.e something like
>>
>> Declare @sql varchar(1000)
>> Declare @mystring varchar( 500)
>>
>> Set @mystring = 'abc'
>> Set @sql = "Select '
>>
>> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>>
>>
>>
>
>

Hi

I am using SQL2000 and if I add
select * from #tblMAIN

straight after I have executed @sql in the same window, it errors with
Invalid object name #tblmain

I.e.
The entire Sp

Declare @sql varchar(1000)

Set @sql = 'CREATE TABLE #tblMAIN (
[STAGE_1_START] [datetime] NULL ,
[ELEMENT] [varchar] (40) NULL)
ON [PRIMARY]'

Exec(@sql)

select * from #tblMAIN

Does it actually run within your environment?

Paul

"dave ballantyne" wrote in message
news:u5B$bUPCJHA.2480-at-TK2MSFTNGP02.phx.gbl...
Hi,

That will work , and does work, but the #table is being drop when it
falls out of scope, the exec statement.

Dave

Paul wrote:
> Thanks that does it.
>
> Whilst I have been doing that , I have constructed a create #tbl string,
> and executed it successfully, but the table does not exist when I try to
> select from it.
>
> I.e.
> Set @sql = 'CREATE TABLE #tblMAIN (
> [STAGE_1_START] [datetime] NULL ,
> [ELEMENT] [varchar] (40) NULL
> ON [PRIMARY]'
>
> Exec(@sql)
>
> Should this work, or is this not possible?
>
> Paul
>
>
> "dave ballantyne" wrote in message
> news:uxrg%23ZOCJHA.1228-at-TK2MSFTNGP02.phx.gbl...
> Hi Paul,
>
> Charindex , should do the job you want
>
> Dave
>
> Paul wrote:
>> I am creating a select statement based on a passed variable.
>>
>> I want to check the string to see if it contains a field from the table
>> and if so add it to my select statement. Is there a function that I can
>> use to check for my field
>>
>> I.e something like
>>
>> Declare @sql varchar(1000)
>> Declare @mystring varchar( 500)
>>
>> Set @mystring = 'abc'
>> Set @sql = "Select '
>>
>> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>>
>>
>>
>
>

Hi

I have googled and found that the table only exists in the scope of the exec
statement, which is no use to me.
Is there a way of holding the table until the end of the sp?

Thanks

Paul

"Paul" wrote in message
news:r8ydnW61nreS5ivVnZ2dnUVZ8uydnZ2d-at-bt.com...
Hi

I am using SQL2000 and if I add
select * from #tblMAIN

straight after I have executed @sql in the same window, it errors with
Invalid object name #tblmain

I.e.
The entire Sp

Declare @sql varchar(1000)

Set @sql = 'CREATE TABLE #tblMAIN (
[STAGE_1_START] [datetime] NULL ,
[ELEMENT] [varchar] (40) NULL)
ON [PRIMARY]'

Exec(@sql)

select * from #tblMAIN

Does it actually run within your environment?

Paul

"dave ballantyne" wrote in message
news:u5B$bUPCJHA.2480-at-TK2MSFTNGP02.phx.gbl...
Hi,

That will work , and does work, but the #table is being drop when it
falls out of scope, the exec statement.

Dave

Paul wrote:
> Thanks that does it.
>
> Whilst I have been doing that , I have constructed a create #tbl string,
> and executed it successfully, but the table does not exist when I try to
> select from it.
>
> I.e.
> Set @sql = 'CREATE TABLE #tblMAIN (
> [STAGE_1_START] [datetime] NULL ,
> [ELEMENT] [varchar] (40) NULL
> ON [PRIMARY]'
>
> Exec(@sql)
>
> Should this work, or is this not possible?
>
> Paul
>
>
> "dave ballantyne" wrote in message
> news:uxrg%23ZOCJHA.1228-at-TK2MSFTNGP02.phx.gbl...
> Hi Paul,
>
> Charindex , should do the job you want
>
> Dave
>
> Paul wrote:
>> I am creating a select statement based on a passed variable.
>>
>> I want to check the string to see if it contains a field from the table
>> and if so add it to my select statement. Is there a function that I can
>> use to check for my field
>>
>> I.e something like
>>
>> Declare @sql varchar(1000)
>> Declare @mystring varchar( 500)
>>
>> Set @mystring = 'abc'
>> Set @sql = "Select '
>>
>> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>>
>>
>>
>
>

Which is what i said...

I assume that you will not know the structure of the temp table at
design time which is why you are creating it in an exec statement.
Correct ?

If you dont know the structure, that would also imply that you are going
to attempt to do all the other data manipulation with dynamic sql ?

That being the the case , and my assumptions being true, that is very
bad practice. Take a step back , have a deep breath and re-evaluate
your process as a whole.

Dave


Paul wrote:
> Hi
>
> I have googled and found that the table only exists in the scope of the exec
> statement, which is no use to me.
> Is there a way of holding the table until the end of the sp?
>
> Thanks
>
> Paul
>
> "Paul" wrote in message
> news:r8ydnW61nreS5ivVnZ2dnUVZ8uydnZ2d-at-bt.com...
> Hi
>
> I am using SQL2000 and if I add
> select * from #tblMAIN
>
> straight after I have executed @sql in the same window, it errors with
> Invalid object name #tblmain
>
> I.e.
> The entire Sp
>
> Declare @sql varchar(1000)
>
> Set @sql = 'CREATE TABLE #tblMAIN (
> [STAGE_1_START] [datetime] NULL ,
> [ELEMENT] [varchar] (40) NULL)
> ON [PRIMARY]'
>
> Exec(@sql)
>
> select * from #tblMAIN
>
> Does it actually run within your environment?
>
> Paul
>
> "dave ballantyne" wrote in message
> news:u5B$bUPCJHA.2480-at-TK2MSFTNGP02.phx.gbl...
> Hi,
>
> That will work , and does work, but the #table is being drop when it
> falls out of scope, the exec statement.
>
> Dave
>
> Paul wrote:
>> Thanks that does it.
>>
>> Whilst I have been doing that , I have constructed a create #tbl string,
>> and executed it successfully, but the table does not exist when I try to
>> select from it.
>>
>> I.e.
>> Set @sql = 'CREATE TABLE #tblMAIN (
>> [STAGE_1_START] [datetime] NULL ,
>> [ELEMENT] [varchar] (40) NULL
>> ON [PRIMARY]'
>>
>> Exec(@sql)
>>
>> Should this work, or is this not possible?
>>
>> Paul
>>
>>
>> "dave ballantyne" wrote in message
>> news:uxrg%23ZOCJHA.1228-at-TK2MSFTNGP02.phx.gbl...
>> Hi Paul,
>>
>> Charindex , should do the job you want
>>
>> Dave
>>
>> Paul wrote:
>>> I am creating a select statement based on a passed variable.
>>>
>>> I want to check the string to see if it contains a field from the table
>>> and if so add it to my select statement. Is there a function that I can
>>> use to check for my field
>>>
>>> I.e something like
>>>
>>> Declare @sql varchar(1000)
>>> Declare @mystring varchar( 500)
>>>
>>> Set @mystring = 'abc'
>>> Set @sql = "Select '
>>>
>>> if contains(@mystring, abc) Set @sql = @sql + 'abc'
>>>
>>>
>>>
>>
>
>
>

Set @sql = 'CREATE TABLE #tblMAIN (
[STAGE_1_START] [datetime] NULL ,
[ELEMENT] [varchar] (40) NULL
ON [PRIMARY]

SELECT * FROM #tblMAIN '

Exec(@sql)