sql server 2012 - Dynamic Operator In SQL -

-

i have problem.

declare @sql nvarchar(max) declare @max int set @max=10  declare @min int set @min=0 declare @oper nvarchar(40) set @oper='>' declare @value int set @value=0 declare @r_sql int set @sql='select @r_sql = case when'+cast(@value int)+cast(@oper nvarchar(1))+@min+' 1 else 0 end '  exec sp_executesql   @sql,   @value,   @min,   @max,   @r_sql output select @r_sql

i want pass dynamic operator execute query says 'error converting data type varchar numeric.' got problem? please me. thanks, appreciate.

the + operator in t-sql can bit wonky. if want concatenate strings, make sure input strings:

set @sql=n'select @r_sql = case when'           +cast(@value nvarchar(max))           +@oper --already nvarchar, , length thing done automatically           +cast(@min nvarchar(max))           +n' 1 else 0 end '

basically, sql server does, when sees +, it's first instinct add 2 numbers.

  • if things on both sides of + numbers, add them
  • if 1 of things number, try convert other , add them (if conversion fails, error message posted)
  • if neither of things number, , sql server try string concatenation.
  • (note in sql server dates special type of numbers)

so, in nutshell:

select 1+2      => yields number 3 select 1+'2'    => yields number 3 select '1'+2    => yields number 3 select '1'+'2'  => yields string '12'  select 1+'a'    => conversion failed error select 'a'+2    => conversion failed error select 'a'+'b'  => yields string 'ab'

alternatively, if on sql server 2012 , above, can use concat function, converts feed string, , concatenates strings

set @sql=concat(n'select @r_sql = case when',                  @value, @oper, @min,                  n' 1 else 0 end ')

Comments

Post a Comment

Popular posts from this blog

windows - Single EXE to Install Python Standalone Executable for Easy Distribution -

-
i used pyinstaller create standalone exe file pyqt project. did not use single file option because exe created single file option takes long open. therefore, pyinstaller created dist folder contains bunch of files including program.exe file. create setup.exe file place contents of dist folder in program directory , create shortcut in start menu , on desktop. want super simple user. maybe setup.exe files when download program cnet. found inno-setup, looks promising. however, not know if there special considerations because program standalone program create python/pyqt program. thanks! have experience task? there program using task not know about? inno-setup or nsis easiest use. tell them files include , put them , create nice installer you. wrote short tutorial on experiences using innosetup might find helpful: http://www.blog.pythonlibrary.org/2008/08/27/packaging-wxpymail-for-distribution/ note tutorial based around wxpython app wrote, concepts same.
Read more

c# - Access objects in UserControl from MainWindow in WPF -

-
i have simple xaml, how can change text property of textblock in usercontrol1 mainwindow ? <window x:class="refactorxaml.mainwindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:refactorxaml="clr-namespace:refactorxaml" title="mainwindow" height="350" width="525"> <grid> <grid.rowdefinitions> <rowdefinition height="100"></rowdefinition> <rowdefinition height="100"></rowdefinition> </grid.rowdefinitions> <grid> <refactorxaml:usercontrol1></refactorxaml:usercontrol1> </grid> <grid grid.row="1"> <button>change text</button> </grid> </grid> </window> <usercon...
Read more

javascript - How to name a jQuery function to make a browser's back button work? -

-
i'm trying write function ajaxyfy web site, works perfect, except function button. that's have now: function loading() { if (typeof history.pushstate !== "undefined") { var historycount = 0; $('.menu-item, .logo').on('click','a',function(e){ e.preventdefault(); $(this).data('clicked', true); if ($(this).is('.logo a')) { var homepage = $(this).attr('href'); function1(homepage); history.pushstate(null, null, homepage); } else if ($(this).is('.projects a')) { var projects = $(this).attr('href'); function2(projects); history.pushstate(null, null, projects); } else { var pages = $(this).attr('href'); function3(pages); history.pushstate(null, null, pages); ...
Read more