run-time changeable queries

I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @.openfile variable. Something like this:
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...

It obviously doesn't work because the variable @.value is not declared within the sql string.
However, since @.openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @.value inside the @.standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.

Any suggestions?I have solved it using a temporary table, rather than a variable, where to store the value val. But if you have a better idea...|||

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||Andranik Khachatryan, I guess you haven't read the code in my first message?

Andranik Khachatryan wrote:

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||

Oops sorry :)

My bad, I am a bit careless today.

|||

You could use sp_executesql with output parameters to do this.

Declare @.StandardSelect nvarchar(4000)
Declare @.Value Int

Select @.StandardSelect = 'Select top 1 @.Value=Id From ' + @.OpenFile
exec sp_executesql @.StandardSelect, N'@.Value int output', @.value OUTPUT

Select @.value

|||

You don't really need dynamic SQL for doing this. You can do below instead:

declare @.source varchar(30)

-- ... initialize based on whether you want to query table or Access

set @.source =

declare @.value int

set @.value = (

select top 1 val from (

select val from your_table where @.source = 'table'

union all

select val from opendatasource(...) where @.source = 'access'

) as t

order by ...

)

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo
> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?
> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

Running Store procedures

Hello there
I have some procedure that i would like to build store procedure for it
If two users who which their name is dbo and run the same store procedure at
the same time, and the store procedure using temp tables. will it cause
problems?
and if so what is the way to deal with these problems?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilHi Roy,
Do you mean the SP will create temp tables for working storage, or that the
temp tables are created outside of the procedure as a global temp table.
Basically, standard temp tables are created using the users connection and
do not overlap with other logged in users. For instance, create table
#mytable creates a table using the users current connection.
Hope this helps
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...

> Hello there
> I have some procedure that i would like to build store procedure for it
> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
> and if so what is the way to deal with these problems?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Whell Phil
The temp tables are created on the store procedure and when the SP ends they
are destroyed
The problem is that all my users are DBO. So my question is: when two users
which their name is DBO, Running the same procedure at the same time on
diffrent data, what will happen?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Roy,
> Do you mean the SP will create temp tables for working storage, or that
the
> temp tables are created outside of the procedure as a global temp table.
> Basically, standard temp tables are created using the users connection and
> do not overlap with other logged in users. For instance, create table
> #mytable creates a table using the users current connection.
> Hope this helps
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...
>
procedure
>|||Hi Roy,
Should not be a problem since the client application they use will open a
new connection per user session. So if two users login as fred, each will
get a different connection to the SQL Server. To check is out do something
like this in QueryAnalyser.
Select tempdb and issue select * from sysobjects where type="U" order by
name
You will see what I mean from this the way that the temp tables are actually
structured within the tempdb database.
Phil
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OoTI8Lu2FHA.3744@.TK2MSFTNGP10.phx.gbl...
> Whell Phil
> The temp tables are created on the store procedure and when the SP ends
> they
> are destroyed
> The problem is that all my users are DBO. So my question is: when two
> users
> which their name is DBO, Running the same procedure at the same time on
> diffrent data, what will happen?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
> news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> the
> procedure
>|||> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
No, each user will get a #temp table in their own scope.
If you look at tempdb..sysobjects you will see names like:
#tempname___________________________4536
7
#tempname___________________________4239
7
(Too lazy to check the exact format of the trailing number/string, but it's
something like that.)
Even better might be reducing or eliminating the need for #temp tables in
the first place.
A