|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query with user-define functionI have a table with 3 fields. In the first field named a there are values
i.e. 5 In the second field named b there are values i.e. 9 In the third field named c there are expressions i.e. a+@q+3*b where a,b supposed to be the contents of the previous fields, different in each row and @q is a variable I wound like to type each time I run the query. I have typed: DECLARE @sql VARCHAR(255) SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' FROM dbo.foo where Index=1 EXEC(@sql) How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? Many Thanks Helen DECLARE @q INT
SET @q = 7 SELECT a,b, a + @q + 3 *b AS c FROM dbo.foo where Index=1 -- Show quoteJacco Schalkwijk SQL Server MVP "Helen" <He***@discussions.microsoft.com> wrote in message news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... >I have a table with 3 fields. In the first field named a there are values > i.e. 5 > In the second field named b there are values i.e. 9 > In the third field named c there are expressions i.e. a+@q+3*b where a,b > supposed to be the contents of the previous fields, different in each row > and @q is a variable I wound like to type each time I run the query. > > I have typed: > DECLARE @sql VARCHAR(255) > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > FROM dbo.foo where Index=1 > EXEC(@sql) > > How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? > > Many Thanks > Helen > I'm sorry. I didn't explain myself correctly. I mean I have this table in a
SQL Server with many rows and different function in each row. Inside the function I would like to have a variable (@q) which I don't know how to write so as when I query I can put a different value each time. Show quote > "Helen" <He***@discussions.microsoft.com> wrote in message > news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... > >I have a table with 3 fields. In the first field named a there are values > > i.e. 5 > > In the second field named b there are values i.e. 9 > > In the third field named c there are expressions i.e. a+@q+3*b where a,b > > supposed to be the contents of the previous fields, different in each row > > and @q is a variable I wound like to type each time I run the query. > > > > I have typed: > > DECLARE @sql VARCHAR(255) > > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > > FROM dbo.foo where Index=1 > > EXEC(@sql) > > > > How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? > > > > Many Thanks > > Helen > > > > > Hi
Maybe CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c varchar(10) ) INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@q+3*b' DECLARE @sql VARCHAR(255) SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo where [Index]=1' FROM dbo.foo where [Index]=1 EXEC(@sql) John Show quote "Helen" <He***@discussions.microsoft.com> wrote in message news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... >I have a table with 3 fields. In the first field named a there are values > i.e. 5 > In the second field named b there are values i.e. 9 > In the third field named c there are expressions i.e. a+@q+3*b where a,b > supposed to be the contents of the previous fields, different in each row > and @q is a variable I wound like to type each time I run the query. > > I have typed: > DECLARE @sql VARCHAR(255) > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > FROM dbo.foo where Index=1 > EXEC(@sql) > > How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? > > Many Thanks > Helen > Yes!! It works. But just a point. In the [Index] column I have both strings
and numbers. When I query [Index]=AA there is an error: Invalid column name 'AA' Any more suggestion? Thank you Helen Show quote "John Bell" wrote: > Hi > > Maybe > > CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c > varchar(10) ) > INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@q+3*b' > > DECLARE @sql VARCHAR(255) > SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo > where [Index]=1' > FROM dbo.foo where [Index]=1 > EXEC(@sql) > > John > > > "Helen" <He***@discussions.microsoft.com> wrote in message > news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... > >I have a table with 3 fields. In the first field named a there are values > > i.e. 5 > > In the second field named b there are values i.e. 9 > > In the third field named c there are expressions i.e. a+@q+3*b where a,b > > supposed to be the contents of the previous fields, different in each row > > and @q is a variable I wound like to type each time I run the query. > > > > I have typed: > > DECLARE @sql VARCHAR(255) > > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > > FROM dbo.foo where Index=1 > > EXEC(@sql) > > > > How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? > > > > Many Thanks > > Helen > > > > > Hi
If your column is a character datatype use 'AA' As quotes will require escaping with a second quote within the string you end up with: CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c varchar(10) ) INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@q+(3*b)' DECLARE @sql VARCHAR(255) SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo where [Index]=''AA''' FROM dbo.foo where [Index]='AA' EXEC(@sql) John Show quote "Helen" <He***@discussions.microsoft.com> wrote in message news:196B3526-043F-40E1-9BD5-F1D6669C093A@microsoft.com... > Yes!! It works. But just a point. In the [Index] column I have both > strings > and numbers. When I query [Index]=AA there is an error: Invalid column > name > 'AA' > Any more suggestion? > > Thank you > Helen > > "John Bell" wrote: > >> Hi >> >> Maybe >> >> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c >> varchar(10) ) >> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@q+3*b' >> >> DECLARE @sql VARCHAR(255) >> SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo >> where [Index]=1' >> FROM dbo.foo where [Index]=1 >> EXEC(@sql) >> >> John >> >> >> "Helen" <He***@discussions.microsoft.com> wrote in message >> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... >> >I have a table with 3 fields. In the first field named a there are >> >values >> > i.e. 5 >> > In the second field named b there are values i.e. 9 >> > In the third field named c there are expressions i.e. a+@q+3*b where >> > a,b >> > supposed to be the contents of the previous fields, different in each >> > row >> > and @q is a variable I wound like to type each time I run the query. >> > >> > I have typed: >> > DECLARE @sql VARCHAR(255) >> > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' >> > FROM dbo.foo where Index=1 >> > EXEC(@sql) >> > >> > How can I write a query where @q=7 to get back 5, 9, 39 >> > (5+7+3*9)?????? >> > >> > Many Thanks >> > Helen >> > >> >> >> Yes!!! It is all right now. I wonder how to write this in visualbasic.net
Thanks Helen Show quote "John Bell" wrote: > Hi > > If your column is a character datatype use 'AA' As quotes will require > escaping with a second quote within the string you end up with: > > CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c > varchar(10) ) > > INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@q+(3*b)' > > DECLARE @sql VARCHAR(255) > SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo > where [Index]=''AA''' > FROM dbo.foo where [Index]='AA' > EXEC(@sql) > > John > > "Helen" <He***@discussions.microsoft.com> wrote in message > news:196B3526-043F-40E1-9BD5-F1D6669C093A@microsoft.com... > > Yes!! It works. But just a point. In the [Index] column I have both > > strings > > and numbers. When I query [Index]=AA there is an error: Invalid column > > name > > 'AA' > > Any more suggestion? > > > > Thank you > > Helen > > > > "John Bell" wrote: > > > >> Hi > >> > >> Maybe > >> > >> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c > >> varchar(10) ) > >> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@q+3*b' > >> > >> DECLARE @sql VARCHAR(255) > >> SELECT @sql = 'DECLARE @q int SET @q=7 SELECT a,b,' + c+ ' FROM dbo.foo > >> where [Index]=1' > >> FROM dbo.foo where [Index]=1 > >> EXEC(@sql) > >> > >> John > >> > >> > >> "Helen" <He***@discussions.microsoft.com> wrote in message > >> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... > >> >I have a table with 3 fields. In the first field named a there are > >> >values > >> > i.e. 5 > >> > In the second field named b there are values i.e. 9 > >> > In the third field named c there are expressions i.e. a+@q+3*b where > >> > a,b > >> > supposed to be the contents of the previous fields, different in each > >> > row > >> > and @q is a variable I wound like to type each time I run the query. > >> > > >> > I have typed: > >> > DECLARE @sql VARCHAR(255) > >> > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > >> > FROM dbo.foo where Index=1 > >> > EXEC(@sql) > >> > > >> > How can I write a query where @q=7 to get back 5, 9, 39 > >> > (5+7+3*9)?????? > >> > > >> > Many Thanks > >> > Helen > >> > > >> > >> > >> > > > Helen,
The T-SQL infix expression evaluator here might help: http://users.drew.edu/skass/SQL/Infix.sql.txt If you first replace the 'a', 'b', and @q in your expression with their values, InFixVal should then evaluate the result. select a, b, dbo.InFixVal(replace(replace(replace(c,'a','('+str(a,19,4)+')'),'b','('+str(b,19,4)+')'),'@q,str(@q,19,4)),1) from ... Also look here, for some examples of its use, and comments about its limitations. It only evaluates a simple set of possible arithmetic expressions, but it may be enough for you. http://groups.google.com/groups?hl=en&q=skass+infix+sqlserver&qt_s=Search Steve Kass Drew University Show quote "Helen" <He***@discussions.microsoft.com> wrote in message news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@microsoft.com... >I have a table with 3 fields. In the first field named a there are values > i.e. 5 > In the second field named b there are values i.e. 9 > In the third field named c there are expressions i.e. a+@q+3*b where a,b > supposed to be the contents of the previous fields, different in each row > and @q is a variable I wound like to type each time I run the query. > > I have typed: > DECLARE @sql VARCHAR(255) > SELECT @sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 ' > FROM dbo.foo where Index=1 > EXEC(@sql) > > How can I write a query where @q=7 to get back 5, 9, 39 (5+7+3*9)?????? > > Many Thanks > Helen >  |
|||||||||||||||||||||||
Other interesting topics