creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgs
SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
need to have a client application do that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs
|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:

> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs
|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:

> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>
>
|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
[vbcol=seagreen]
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
|||gain, prompting a user need to be done in your client applications. You cannot write the whole
application in SQL only, SQL is only a data retrieval and modification language. The interaction
with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
something like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...[vbcol=seagreen]
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:

> gain, prompting a user need to be done in your client applications. You cannot write the whole
> application in SQL only, SQL is only a data retrieval and modification language. The interaction
> with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
>
>

creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgsSQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
need to have a client application do that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:
> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs
>
>|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs|||gain, prompting a user need to be done in your client applications. You cannot write the whole
application in SQL only, SQL is only a data retrieval and modification language. The interaction
with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
something like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
>> You
>> need to have a client application do that.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>> > Hi,
>> >
>> > i am trying to create a user defined function that return a table and it
>> > prompts for two inputs
>> >
>> > i have created the functions but how do i get the system to prompt me for
>> > the inputs via a view ?
>> >
>> > appreciate any advise
>> >
>> > tks & rdgs
>>|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:
> gain, prompting a user need to be done in your client applications. You cannot write the whole
> application in SQL only, SQL is only a data retrieval and modification language. The interaction
> with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> > Hi ,
> >
> > does a view able to prompt the user for the parameters then , i can only
> > open a view as a query and where it allowed me to create parameters but it
> > seems like there's no way for me to save those changes at all
> >
> > is there other way thru using a view ?
> >
> > tks & rdgs
> >
> > "Tibor Karaszi" wrote:
> >
> >> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
> >> You
> >> need to have a client application do that.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> >> > Hi,
> >> >
> >> > i am trying to create a user defined function that return a table and it
> >> > prompts for two inputs
> >> >
> >> > i have created the functions but how do i get the system to prompt me for
> >> > the inputs via a view ?
> >> >
> >> > appreciate any advise
> >> >
> >> > tks & rdgs
> >>
> >>
> >>
>
>

creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgsSQL Server cannot prompt a user for anything, as it runs on the server machi
nes, as a service. You
need to have a client application do that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:

> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:

> SQL Server cannot prompt a user for anything, as it runs on the server mac
hines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>
>|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
[vbcol=seagreen]
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
>|||gain, prompting a user need to be done in your client applications. You cann
ot write the whole
application in SQL only, SQL is only a data retrieval and modification langu
age. The interaction
with the user need to be done with a client application, written in VB. C#,
C++ Delphi Access or
something like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...[vbcol=seagreen]
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
>|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:

> gain, prompting a user need to be done in your client applications. You ca
nnot write the whole
> application in SQL only, SQL is only a data retrieval and modification lan
guage. The interaction
> with the user need to be done with a client application, written in VB. C#
, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
>
>

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

creating databases on the fly

hello,

i am trying to create a database by using a store procedure. This stored procedure takes two input parameters. i want to assign these parameters to the 'Filename' attributes when i'm creating the database both for the .mdf and .ldf files. However i keep getting an error.

These work ---
FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.mdf',

FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',

These do'nt work---
FILENAME = @.Databasepath,
FILENAME = @.Databaselogpath,

Here is my code:
------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@.Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @.Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]

CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @.Databasepath,
--FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @.Databaselogpath,
--FILENAME ='c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

I am still researching my problem but i would appreciate any help. Thanks guys.

JamaicanGuy
**I am a newbie .net developer.
Jah Bless!!!!!!!!!!!!Use dynamic sql:
CREATE PROCEDURE rico_dbasescript
@.Databasepath varchar(100) = 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdb.mdf' , @.Databaselogpath varchar(100)= 'c:\program files\microsoft sql server\mssql$sardonyxinstance\data\Sardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]

declare @.SQLString varchar(8000)

set @.SQLString = 'CREATE DATABASE Sardonyxrioctestdb ON
(NAME = ''Sardonyxrioctestdb_dat'',
FILENAME = ' + @.Databasepath + ',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = ''Sardonyxrioctestdb_log'',
FILENAME = ' + @.Databaselogpath + ',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'

execute (@.SQLString)
GO