Question about sp_executesql

Hi all,
I just wanted to know why this doesn't work: if @.1's values is computer
--------------------------------
BEGIN
FETCH NEXT FROM keyword_cursor into @.1

SELECT @.sql = @.sql + 'where title LIKE ' +'''%@.x1%''' + ' OR notes like ' +'''%@.x1%'''

SELECT @.paramlist ='@.x1 nvarchar(200)'
print @.sql
EXEC sp_executesql @.sql, @.paramlist, @.1

RETURN 0
END
The @.sql string evaluates to:
select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'
--------------------------------
But this works:
BEGIN
FETCH NEXT FROM keyword_cursor into @.1

SELECT @.sql = @.sql + 'where title LIKE ''%''+ @.x1 + ''%'' OR notes like ''%'' + @.x1 + ''%'''

SELECT @.paramlist ='@.x1 nvarchar(200)'
print @.sql
EXEC sp_executesql @.sql, @.paramlist, @.1

RETURN 0
END
The @.sql string evaluates to:
select title, notes from pubs..titles where title LIKE '%'+ @.x1 + '%' OR notes like '%' + @.x1 + '%'
----------------------------------
I just don't get it ?? Doesn't sp_executesql just replaces the @.x1 with @.1?

I don't believe so. It will execute the exact string you passed in.
select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'
look at that for instance. If you ran that in query analyzer, itwould not replace '%@.x1%' with anything because it's infact in thestring. @.x1 should be treated as a variable, and must be outsideof any string delimiter such as '.
Another example just in case is..
Dim strName as String = "KraGiE"
Response.Write("hello strName")
That would not replace strName with "KraGiE" because it's not being used as a variable.
|||Hi KraGie,
Thanks for your reply and explaination. Now i understand why the string "select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'". But i still don't get why the second string "select title, notes from pubs..titles where title LIKE'%' + @.x1 + '%' OR notes like'%' + @.x1 + '%'" will work. Because after the replacement of the variable @.x1 with "computer" then it will evaluate to select title, notes from pubs..titles where title LIKE %computer% OR notes like %computer% right? without the single quotes outside the percentage signs won't the statement generate an error??

Question about Running Totals in a Matrix

I am having problems specifying a running total within a matrix. I have
monthly values in the columns of the matrix so that I am displaying Month To
Date (MTD) sales numbers in the data. I would like to use a running total
across months to get a YTD report. I am trying to add a row group to the
matrix and define an expression referencing the RunningValue of the
salesamout field.
I get errors about running values cannot be used in group expressions. How
would I solve this problem?You can use the RunningValue function in matrix headers and in matrix cells
(which seems what you want to achieve), but not in the matrix grouping
expression. Therefore, just use a grouping expression like
=Fields!Month.Value for the column grouping and use a function in the matrix
cell similar to: =RunningValue(Fields!Sales.Value, Sum,
"CategoryYearGrouping") - where CategoryYearGrouping would represent the
parent group of the month grouping. See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_5tt1.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:6B23ECD0-8C7F-4AF1-A414-1A712E686303@.microsoft.com...
> I am having problems specifying a running total within a matrix. I have
> monthly values in the columns of the matrix so that I am displaying Month
To
> Date (MTD) sales numbers in the data. I would like to use a running total
> across months to get a YTD report. I am trying to add a row group to the
> matrix and define an expression referencing the RunningValue of the
> salesamout field.
> I get errors about running values cannot be used in group expressions. How
> would I solve this problem?

question about retrieving identity value

hi,
according to the SQL Server Books Online, there're 3 system functions return
last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
...
BEGIN TRAN
...
INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
...
COMMIT TRAN
...
I think using IDENT_CURRENT may be a problem in multiuser environment,
because it returns the last identity value generated in any session. But he
said it's ok, since these statements are put in a transaction and SQL Server
will lock the table. Is it true?Hi
He is wrong. SQL Server may not lock the whole table, so it is possible that
2 processes can insert into the same table, at the same time.
SCOPE_IDENTITY is the correct one to use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:72F8BDEC-FA0F-4880-B1E7-FFBDB8102955@.microsoft.com...
> hi,
> according to the SQL Server Books Online, there're 3 system functions
> return
> last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
> SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
> ...
> BEGIN TRAN
> ...
> INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
> SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
> ...
> COMMIT TRAN
> ...
> I think using IDENT_CURRENT may be a problem in multiuser environment,
> because it returns the last identity value generated in any session. But
> he
> said it's ok, since these statements are put in a transaction and SQL
> Server
> will lock the table. Is it true?

question about delete statement

Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !Can you give more details on what you are trying to do? I suspect that there are better ways to accomplish whatever you need to do.

To answer your question as it was posted, you could create a trigger on the T2 table that updated the T1 table appropriately. I'm a little vague on the implementation details because it seems that you are looking for more than a simple counter, and you haven't provided a lot of details on what you want to happen.

-PatP|||CREATE TRIGGER myTriggerForDelete
ON T2
AFTER DELETE
AS
UPDATE T1
SET C1=COALESCE((SELECT MIN(C1) FROM T1),0)-1|||It should be something like my previous post.

But as Pat said, I don't think this makes senses, maybe you should give us more explanations.

Hope it can helps you|||please go to books online and read the article on CREATE TRIGGER and pay special attention to the section on the inserted and deleted tables.|||Are you trying to prevent "holes" in a meaningless "key"|||Are you trying to prevent "holes" in a meaningless "key"

If it's the case you should read that:
http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml

question about charts

I have a pie chart which is been populated from a sproc.

and for a particular fund the values that generate are 78%,20% and 2% respectively.

And when it generates a chart the pie chart is uniformly displayed... Like for eg.. its been broken into 3 parts and there isnt a difference between the 78% and the 2% so ... How can i get the pie chart to be a displayed according to its values..

Any help will be appreciated..

Regards

Karen

So you are saying that each piece of the pie is showing as one third of the pie instead of being divided according to the percentage if you have three values in the pie chart?

Have you tried an exploded pie with 3D effects? Have you displayed the value for each piece of the pie on the chart?

Since it is a pie chart, it should naturally divide the pie according to the values in the chart.

If not, it would be like a bar graph with all of the bars at the same height when the values were in fact different.

|||

i have a exploded pie chart ... in design mode i can see the differences but not when i preview it..

Regards

KAren

|||

So when you preview it you can see the percentages over the pieces of the pie, but it looks as if it's not dividing it accordingly?

I'm making sure I understand the problem...

|||Maybe it's having a problem with the percentage itself. Is your field in itself a percentage? If so, I don't think it should be. The field itself should be a number (not a percentage) and the pie chart will divide the number by the total to get the percentage behind the scenes. You don't need to make it a percentage yourself.|||

greg,

The fields is varchar field in Sql server and i am not doing any thing special in the report..

78% part should be bigger than 2 % but 78% and 2% are the same size.

So how can i solve it

Regards

Karen

|||

i changed the datatype to decimal and it works fine.

Thanks

Regards,

Karen

question about best way to store an up or down value

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-DavidWithout getting into design issues, the best way would be to use a BIT datatype, with 0 used to indicate FALSE or OFF, and 1 to indicate TRUE or ON.